0

I am trying to copy some 8-digit numbers to use in a SQL search.

The SQL query gave me errors and after some debugging I found that the string doesn't contain all the data. It seems that after 25 or so numbers my for loop stops entering data as if the string is full.

Thanks for the help...

Lots = ""
For iRow = 2 To 500
    If IsEmpty(Sheets("Filtered Data").Cells(iRow, 2)) Then Exit For
    Lots = Lots & ",'" & Sheets("Filtered Data").Cells(iRow, 2).value & "'"
Next iRow

Lots = "(" & Mid(Lots, 2, Len(Lots) - 1) & ")"
Alex P
  • 12,249
  • 5
  • 51
  • 70
Barry Aibinder
  • 3
  • 2

2 Answers2

0

Your code works fine. Presumably you have an empty cell in the column which is making it exit the loop....

Jpad Solutions
  • 332
  • 1
  • 12
  • Can you add this as a comment to the question rather than as an answer? – Alex P Aug 19 '16 at 10:28
  • apparently not, if you don't have a 50 reputation – Jpad Solutions Aug 19 '16 at 10:29
  • The data has no empty cells. i'm using the same code here in another macro and its working fine there, but in my new macro it seems that the string has limits. – Barry Aibinder Aug 19 '16 at 10:55
  • Assuming Lots is declared as a string, it doesn't have a limit that will be affecting your data. See http://stackoverflow.com/questions/10927764/vba-string-limit So it must be an issue somewhere else. Without having all of the code, and the file you are using it is very hard to help – Jpad Solutions Aug 19 '16 at 11:05
  • @BarryAibinder I tried it on 150 8 digit numbers and it worked fine. There must be more to your code or data than meets the eye. – Jpad Solutions Aug 19 '16 at 11:40
0

you should post your data raising the errors

as for while you can consider the following code to build-up the string exploiting Join() function

Dim Lots As String

With Worksheets("Filtered Data") '<--| change "Filtered Data" with your actual worksheet name
    With .Range("B2", .Cells(.Rows.Count, 2).End(xlUp)) '<-- consider its column "B" cells from row 2 down to last non empty one
        Lots = "('" & Join(Application.Transpose(.Value), "','") & "')" '<-- build up the string
    End With
End With

this assumes that all non empty cells in column "B" are contiguous (i.e. non blank cells in between non blank ones), but it can be easily modified should this not be the case

user3598756
  • 28,893
  • 4
  • 18
  • 28
  • i tried you code, but the issue still remains. The Lots string only collects the first 26 numbers and doesn't ads the finel " ') ". I'm sorry but i cannot upload all the code or the data i'm working on. – Barry Aibinder Aug 19 '16 at 11:24
  • you could upload the first 27 numbers only, in order to help people helping you – user3598756 Aug 19 '16 at 11:27
  • Sorry, can't upload my data(IP issues). But it doesn't matter what numbers there are, just try it on a 27 8-digit numbers. – Barry Aibinder Aug 19 '16 at 11:35
  • I tried with 28 numbers: `12345678`, `12345679`,...,`12345705` and it correctly returned:`('12345678','12345679','12345680','12345681','12345682','12345683','12345684','12345685','12345686','12345687','12345688','12345689','12345690','12345691','12345692','12345693','12345694','12345695','12345696','12345697','12345698','12345699','12345700','12345701','12345702','12345703','12345704','12345705')` – user3598756 Aug 19 '16 at 11:43
  • are your data actually in contiguous cells from row 2 downwards? – user3598756 Aug 19 '16 at 12:03
  • yes. i have tried to place the Lots string in a cell and it seems OK now. but now i'm having issues passing it to my SQL Function but without any success. – Barry Aibinder Aug 19 '16 at 12:21
  • Now it looks like a SQL related syntax problem: you may want to open a new post with your current SQL command problem. As for this post problem If my answer resolved it please mark it as accepted. thank you – user3598756 Aug 19 '16 at 12:37