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!
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!