Excel and VBA

[kdigital]

Ok I need some suggestions or help with something. In my head it's easy but getting into excel syntax seems to be more difficult.

This is the project:
I have created lookup tables of data that is stored in other workbooks within our company. These tables are within the workbook that I am creating as basically a report generator.
The idea was to use vlookup to extract the data out of the lookup tables and put the values into the cells on a report worksheet.

I recorded a macro to do just that or a series of them. My problem is I just can't get my head around how to make the programming more generic. WHat I want to do is grab a value from a cell say the cell is h3...now on row 3 from A through F .. the cells are to be filled from vlookups.

I don't knkow if this make sense.. I just can't get my head around how to refer to cells as variables.

Thanks for any help.

[lucas]

You are trying to use vlookup on closed workbooks?

[kdigital]

no, I copied the data from the worksheets in the workbooks I needed into a single workbook. Then made lookup tables from that data. It's all in the same workbook

[lucas]

So you have your vlookups working, now you want to transfer those results to a report sheet.....

could you post and example with dummy data?

[kdigital]

Here is a sample of the reports page. and the vlookup..All I need is code that will basically fill in when a person types in the account. I know how to do that with the worksheet change event...I am just having trouble with getting the code to put in the vlookup formula in the cells.

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nRow As Long

On Error GoTo ws_exit:
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("H3")) Is Nothing Then

With Target

nRow = Me.Range("A1").End(xlDown).Row + 1
Me.Cells(nRow, "A").Formula = "=VLOOKUP(D" & nRow & ",lookuptable!A2:E6,2,FALSE)"
Me.Cells(nRow, "B").Formula = "=VLOOKUP(D" & nRow & ",lookuptable!A2:E6,3,FALSE)"
Me.Cells(nRow, "C").Formula = "=VLOOKUP(D" & nRow & ",lookuptable!A2:E6,4,FALSE)"
Me.Cells(nRow, "D").Value = .Value
Me.Cells(nRow, "E").Formula = "=VLOOKUP(D" & nRow & ",lookuptable!A2:E6,5,FALSE)"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[lucas]

I don't understand. If I type in a new account number into cell D2 the vlookups work and all the correct data is reported on row 2.

Are you wanting the vlookup to work on something besides an account input?

[kdigital]

yes it works fine..but say you type in a account number in the next cell down d3....then you want for a new account for excel to put the data in...It looks like xld has the code.

[lucas]

Bob is intuitive in understanding peoples needs here.......

[kdigital]

First I'd like to thank you Bob for your help however I decided it would be better if the user of the report form could put in all the accounts and then just click a button that would run the macro or code to fill in the cells. I tried my hand at tweaking the code to do this but it seems I am missing something.

Sub fillincell()
Dim nRow As Long
Dim selection As Range


Set selection = Reportsheet.Range("h3.h200")

nRow = selection("A1").End(xlDown).Row + 1
selection(nRow, "A").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,2,FALSE)"
selection(nRow, "B").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,6,FALSE)"
selection(nRow, "C").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,3,FALSE)"
selection(nRow, "D").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,2,FALSE)"
selection(nRow, "E").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,3,FALSE)"
selection(nRow, "F").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,4,FALSE)"
selection(nRow, "G").Formula = "=VLOOKUP(H" & nRow & ",vollookup!A2:f600,4,FALSE)"
selection(nRow, "H").Value = Reportsheet.cell("h3").Value
selection(nRow, "I").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,5,FALSE)"
selection(nRow, "J").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,6,FALSE)"
selection(nRow, "K").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,7,FALSE)"
selection(nRow, "L").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,8,FALSE)"
selection(nRow, "M").Formula = "=VLOOKUP(H" & nRow & ",Mreportnlookup!A2:i600,9,FALSE)"
selection(nRow, "R").Formula = "=VLOOKUP(H" & nRow & ",trliqlookup!A2:i600,2,FALSE)"
selection(nRow, "S").Formula = "=VLOOKUP(H" & nRow & ",trliqlookup!A2:i600,3,FALSE)"


End Sub

That is a sample of where I was...any assistance would be appreciated. I think I'm stuck on variable and ranges and just how to set them.

Thanks,
Kevin