vlookup to select sheet

[Guest]

need help, trying to use vlookup on a simple table to find sheet/tab name, go
to that tab, drop in some data, and then return to table. any help greatly
appreciated!

 

[Mike Fogleman]

We need a little more detail on where things are located. VLookup may not be
the best way to do this. Perhaps an example of what you have now and what
you want it to look like. Is the data to drop in the LookUp table? Where
does the sheet name come from for VLookup to find? Where exactly do you want
the data dropped and do you want to append it or overwrite it? etc..etc.

Mike F

 

[Guest]

hi mike, thanks for reply. turns out i'm not using vlookup function, since
the table where i'm pulling information is pretty much fixed and is just
updated periodically, i'm using loop to go through table row by row. did end
up using "match" function to determine what row to place payment amount
within each tab. this seems to work, see VBA below: (sheet1 has the table
which has bloomburg formulas, behind sheet1 is a sheet for each account
listed in table. table has four columns: acct no., sheet name, pymt amount,
pymt date):

Sub Macro()

Sheets("sheet1").Activate

For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1

Sheets("sheet1").Activate
Dim amt As Currency
amt = Cells(i, 3).Value

Dim date As Single
date = Cells(i, 4).Value

Dim sheetname As String
sheetname = Cells(i, 2).Value

Sheets(sheetname).Activate

Dim drop As Integer
drop = WorksheetFunction.Match(date, Range("b14:b114"), 1) + 13

Cells(drop, 3).Value = amt
Next i

End Sub

i'm novice in VBA and would appreciate any comments regarding above. oft
times i'm not aware of logic that is behind code, just pull it off discussion
board, tweak it until it works for what we need.