PDA

View Full Version : Solved: Find in VBA



khalid79m
12-11-2009, 09:20 AM
Dim rge_find As Range
Dim int_count As Integer
int_count = 0
Do Until Workbooks(Str_Wkb1).Sheets(Str_Wksht_1).Cells(2 + int_count, 11).Value = ""

Set rge_find = Workbooks(Str_Wkb1).Sheets(Str_Wksht_1).Columns(1).Find(Workbooks(Str_Wkb1) .Sheets(Str_Wksht_1).Cells(2 + int_count, 11).Value)
If Not rge_find Is Nothing Then
Workbooks(Str_Wkb1).Sheets(Str_Wksht_1).Cells(2 + int_count, 10).Value = Workbooks(Str_Wkb1).Sheets(Str_Wksht_1).Cells(2 + int_count, 11).Value
Else

If Workbooks(Str_Wkb1).Sheets(Str_Wksht_1).Cells(2 + int_count, 12).Value = "" Then
GoTo L_Add_Error
End If

Set rge_find = Workbooks(Str_Wkb1).Sheets(Str_Wksht_1).Columns(1).Find(Workbooks(Str_Wkb1) .Sheets(Str_Wksht_1).Cells(2 + int_count, 12).Value)
If Not rge_find Is Nothing Then
Workbooks(Str_Wkb1).Sheets(Str_Wksht_1).Cells(2 + int_count, 10).Value = Workbooks(Str_Wkb1).Sheets(Str_Wksht_1).Cells(2 + int_count, 12).Value
Else
L_Add_Error:
Workbooks(Str_Wkb1).Sheets(Str_Wksht_1).Cells(2 + int_count, 10).Value = "|EwFMSn:ECRSn|Er"
End If

End If

int_count = int_count + 1
Loop

the above is my code, Im not sure what I have done wrong,

I have list of staffnumber in column 1, and i have a list of staff numbers in column 11 and 12.

I want the above code to go through column 11 (k2 till the last row of data) and see if the staff number exists in column 1

if it does then paste the staff number in column 10
if it doesnt the check of column 12 is blank,

if it is blank then display "|EwFMSn:ECRSn|Er"
if it is not blank the check the staff number in column 12 againts column 1.

if exsist the paste the staff number in column 10
if it is blank then display "|EwFMSn:ECRSn|Er"


Can anyone help ?

I previously had this formula doing this
Workbooks(Str_Wkb1).Sheets(Str_Wksht_1).Range("J2").FormulaR1C1 = _
'"=IF(COUNTIF(R2C1:R65536C7,RC11)=1,RC11,IF(COUNTIF(R2C1:R65536C7,RC12)=1,RC1 2,""|EwFMSn:ECRSn|Er""))"

but it takes forever to run . but works!

Simon Lloyd
12-11-2009, 11:58 AM
You should supply a workbook as this is a little confusing!

p45cal
12-11-2009, 04:20 PM
You haven't said what was going wrong.. Try something like this (I haven't been able to test it - so a version of your workbook with any sensitive data removed or replaced with made up data would be useful). I took out the GoTo and made it a bit easier to read (but stuffed it with comments!):
Dim rge_find As Range
Dim int_count As Long, StaffNumber
Dim DestCell As Range
int_count = 2
With Workbooks(Str_Wkb1).Sheets(Str_Wksht_1)
Do Until .Cells(int_count, 11).Value = ""
Set DestCell = .Cells(int_count, 10) 'Destination Cell
StaffNumber = .Cells(int_count, 11).Value 'First staff no. to seek
Set rge_find = .Columns(1).Find(StaffNumber) 'is it in column1?
If Not rge_find Is Nothing Then 'yes it is
DestCell.Value = StaffNumber 'put it in the destination cell
Else 'no it's not
StaffNumber = .Cells(int_count, 12).Value 'Second Staff no. to seek
If StaffNumber <> "" Then Set rge_find = .Columns(1).Find(StaffNumber) 'only look for it if second staff no. isn't blank
'if it's there put it in the destination cell otherwise it's a blank or not found so put that string in instead.
If Not rge_find Is Nothing Then DestCell.Value = StaffNumber Else DestCell.Value = "|EwFMSn:ECRSn|Er"
End If
int_count = int_count + 1
Loop
End With 'Workbooks(Str_Wkb1).Sheets(Str_Wksht_1)

khalid79m
12-18-2009, 09:40 AM
This does exatcly what I wanted, your a star,.