Solved: Find in VBA

[khalid79m]

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,RC12,""|EwFMSn:ECRSn|Er""))"

but it takes forever to run . but works!

[Simon Lloyd]

You should supply a workbook as this is a little confusing!

[p45cal]

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!):
[vba]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)
[/vba]

[khalid79m]

This does exatcly what I wanted, your a star,.