0

I have created 2 separate Excel spreadsheet, 1 for my database and 1 for my sorting automation and VBA. It's basically look like this.

database

vba I am trying to add pole number to my end data (image 2) with VLOOKUP in VBA. I hope I can show pole number depend on the identifier number, so I used column 2 and 3. My code:

Dim i1 As Integer
Dim i2 As Integer
Dim t As Integer

i1 = Range("B3").Value
i2 = Range("E3").Value

For t = 0 To (i2 - i1)

Cells(t + 7, 1).Value = i1
Cells(t + 7, 2).Value = [VLOOKUP(i1,database2!B:C,2,FALSE)]
i1 = i1 + 1

Next t

But the result keep showing #N/A. Any help? Thanks

R. Gunawan
  • 35
  • 7
  • Which is the value of i2? Also you are using i1 before you give it value. – Error 1004 Mar 05 '19 at 08:09
  • @Error1004 I was about to ask the same thing... i1 & i2 are declared, but never assigned, so default value is 0. – FAB Mar 05 '19 at 08:12
  • My bad. i1 and i2 following the value from the B3 and E3. I edited it on my question too. – R. Gunawan Mar 05 '19 at 08:12
  • @R.Gunawan it will be more helpfull if we can see column letters and row numbers. if understand right in cell B3 & E3 there are 2 values in each cell. right? – Error 1004 Mar 05 '19 at 08:13
  • I suggest adding the vlookup formula manually and see if it does what you expect that way, if not adjust around, and then apply in VBA. – FAB Mar 05 '19 at 08:14
  • This might also help ( [using-vlookup-in-vba-to-reference-another-excel-file](https://stackoverflow.com/questions/32744128/using-vlookup-in-vba-to-reference-another-excel-file?rq=1) ). – FAB Mar 05 '19 at 08:17
  • @Error1004 edited already. thanks – R. Gunawan Mar 05 '19 at 08:17
  • 1
    try using Cells(t + 7, 2).formula = "=VLOOKUP(i1,database2!B:C,2,FALSE)" in instead of .value. But I assume this may arise reference issue – sammyyy Mar 05 '19 at 08:24
  • @YY - `i1` is a variable, you are passing it as a string in the formula. – Vityata Mar 05 '19 at 08:58
  • Maybe you have training or leading spaces? Or, check for the existence of some other non-printable characters. – ASH Mar 05 '19 at 13:35

0 Answers0