Recent Feedback
I want to use arrays in my program. Here's my declaration: Dim zSiteName() As String There are several arrays right, now. Right after I start I find out how many rows I will need. How do I change the array size to that variable value to take the memory drain off the system? and where do I do it? Do I do a ReDim on the Array in the program. There is no values to save at this point?
Optional Information: Language (or Software): Excel Browser (if necessary): IE
Hello and welcome to JustAnswer. I look forward to assisting you today with your question and providing the best answer possible.hithanks for using justAnswer.comyou can resize the array using ReDimDim zSiteName() As String =New String(10)ReDim zSiteName(2)change the array size from 10 to 2but using ReDim , data in the previous array will lost . For avoiding this, use Preserve with ReDimRemember, if for any reason you are unsatisfied with my answer, we can continue the conversation until you’re satisfied and Accept my answer. I can address follow up questions at no extra charge and I’m always here to help. Thank you for using JustAnswer!
Experience: Master of Computer Application plus 5+ year experience in Software development
I'm running my program loading my array and displaying it afterward using this message box command:
MsgBox ("Row= " & i & zSiteName(i) & " " & zAddress(i) & " " & zRoute(i) & " " zDriver(i) & Chr(10) & Chr(13) _ & " " & CB(i) _ & " " & HB(i) _ & " " & RL(i) _ & " " & AL(i) _ & " " & DS(i) _ & " " & SN(i) _ & " " & FV(i) _ & " " & PS(i))
It says its's a syntax error.
It was working until I added a couple in the middle. Is there a length problem? Or something else.
Never mind that last question. I solved it myself. Thanks.
hiok
Sub RetrieveSite_Click() zDate = InputBox("Please, enter a date") u = Range("A" & Rows.Count).End(xlUp).Row "u = 25" u = u - 1 "u = 24" MsgBox ("Date & Range " & zDate & " " & u) "Works fine" ReDim zSiteName(u) ReDim zAddress(u) ReDim zRoute(u) ReDim zDriver(u) ReDim CB(u) ReDim HB(u) ReDim RL(u) ReDim AL(u) ReDim DS(u) ReDim SN(u) ReDim FV(u) ReDim SP(u) ReDim PS(u) ReDim BL(u) For i = 2 To u "2 to 24" zSiteName(i) = Cells(i, 1) zAddress(i) = Cells(i, 2) zRoute(i) = Cells(i, 3) zDriver(i) = Cells(i, 4) CB(i) = Cells(i, 5) HB(i) = Cells(i, 6) RL(i) = Cells(i, 7) AL(i) = Cells(i, 8) DS(i) = Cells(i, 9) SN(i) = Cells(i, 10) FV(i) = Cells(i, 11) SP(i) = Cells(i, 12) PS(i) = Cells(i, 13) BL(i) = Cells(i, 14) MsgBox ("Row= " & i & zSiteName(i) & " " & zAddress(i) & " " & zRoute(i) & " " & zDriver(i) & Chr(10) & Chr(13) _ & " " & CB(i) _ & " " & HB(i) _ & " " & RL(i) _ & " " & AL(i) _ & " " & DS(i) _ & " " & SN(i) _ & " " & FV(i) _ & " " & SP(i) _ & " " & PS(i) _ & " " & BL(i)) Next i " Goes to 24 showing correct values, then a box appears stating that subscript is out of range, instead of stopping"
hisorry i am not too much experienced in VBAyou put a new question for this