Trying to run FIND to locate rows in worksheets across my workbook

[ca11059]

Hi, I am trying to locate a specific value (using FIND) in a different ws and then need to get the ROW info so I can grab data from cells in that row to populate in my active worksheet... I pasted the code I'm working with below, but I am guessing at how to get the row info and have been unable to find it..

I started using VLOOKUP - but for some reason the variable isn't picking up the returned value, not sure what I'm doing wrong there. The If stmt at the very end seems to be working correctly. I'm open to using either FIND or VLOOKUP.

Assnmnt and AcctDesc are both named ranges within the worksheets.

Also, sorry I don't know how to paste the code so neatly like everyone else does!




Dim LastRw As Long, Rw As Long, Cnt As Long, CrntRw As Long, R As Long
Dim dSht As Worksheet, dSht2 As Worksheet, dSht3 As Worksheet, tSht As Worksheet, Tst As Worksheet
Dim MakeBooks As Boolean, SavePath As String
Dim Acct As String
Dim wtf As Range

Application.ScreenUpdating = False 'speed up macro execution
Application.DisplayAlerts = False 'no alerts, default answers used

Set dSht = Sheets("GroupMembership-Aug") 'sheet with data on it starting in row2
Set dSht2 = Sheets("CertAssign")
Set dSht3 = Sheets("ACTIVEACCOUNTS")
Set tSht = Sheets("Template") 'sheet to copy and fill out
Cnt = 0

'Determine last row of data then loop through the rows one at a time
LastRw = dSht.Range("A" & Rows.Count).End(xlUp).Row

For Rw = 2 To LastRw
Acct = dSht.Range("E" & Rw)

On Error Resume Next
Set Tst = ActiveWorkbook.Worksheets(Acct)
'Verify that the proposed sheet name does not already exist in the workbook.
If Tst Is Nothing Then

tSht.Copy After:=Worksheets(Worksheets.Count) 'copy the template
With ActiveSheet 'fill out the form
'edit these rows to fill out your form, add more as needed
.Name = Acct
.Range("E2").Value = dSht.Range("B" & Rw).Value
.Range("B2").Value = dSht.Range("F" & Rw).Value
.Range("H2").Value = dSht.Range("C" & Rw).Value


Set wtf = dSht3.Range(AcctDesc).Find(Acct)
R = wtf.Row

.Range("B4").Value = dSht3.Range("I" & R).Value
wtf = Application.VLookup(Acct, dSht2.Range(Assnmnt), 5, False)
.Range("B6").Value = wtf
wtf = WorksheetFunction.VLookup(Acct, dSht2!Assnmnt, 7, False)
.Range("B7").Value = wtf
wtf = WorksheetFunction.VLookup(Acct, dSht2!Assnmnt, 9, False)
.Range("B8").Value = wtf
wtf = WorksheetFunction.VLookup(Acct, dSht2!Assnmnt, 3, False)
If WorksheetFunction.VLookup(Acct, dSht2!Assnmnt, 3, False) = "Group" Then
.Range("H2").Value = Mid(Acct, 12, 5)

End If
End With
Cnt = Cnt + 1
End If

 

[ca11059]

I am also open to VLOOKUP, but read that FIND is faster, it will have to search through up to 200k records.. anyway, here is the VLOOKUP line that "almost" works - any help would be appreciated, definitions below the code:


wtf = Application.WorksheetFunction.VLookup(Sheets(Acct).Range("E2").Value, dSht3.Range("AcctDesc"), 7, False)

Acct is a string value in the macro (used to name the sheet that is currently active).
I want to search on the value in E2.
dSht3 is a worksheet in the macro
AcctDesc is a Named Range in the workbook (not sure if I called this correctly).
I want the value(string) in the 7th column to the right of the searched value.

Thanks,
Cynthia

 

[ca11059]

Figured out that my code was OK, one of the columns had numbers formatted as text and the other sheet wasn't.