Dynamic arrays and type mismatches

  • Thread starter Thread starter carg1
  • Start date Start date
C

carg1

Hey folks, hope you're all good tonight. I'm having a problem with a
type mismatch first and foremost, and need a little guidance on dynamic
arrays. This is a search macro. I want to find an item number, have it
display any values for the item (which will always be in columns B:G),
and then ask if any values need to be added or changed (haven't gotten
to that part yet). I was doing some preliminary testing, and the macro
stops in error at

Code:
--------------------
If Worksheets(1).Columns(i).Formula <> "" Then
--------------------

citing a type mismatch as the problem. I tried a few ways around this
using the Rows and Columns properties, trying Range, but I get errors
ranging from that to global class failures. I'm somewhat new to this,
using arrays and ifs anyway, so this is perplexing me and I'm sure its
something simple. Any suggestions?


PHP code:
--------------------
Sub Search()

Dim TNum(), Term2, Iput, Iput2 As String
Dim j, k As Integer
j = 0

Iput = InputBox("Please enter item:") 'Input
Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByRows).Activate 'Find it

For i = 2 To 7

If Worksheets(1).Columns(i).Formula <> "" Then
j = j + 1

ReDim TNum(1 - 6)
TNum(j) = Worksheets(1).Columns(i).Formula
End If
Next i

Term2 = MsgBox("The values for this item are:" & _
vbCrl & _
TNum(1) & ", " & TNum(2) & ", " & TNum(3) & ", " & _
TNum(4) & ", " & TNum(5) & ", " & TNum(6), _
vbYesNoCancel + vbDefaultButton2, "Tariff Numbers")
 
two things here

(1) re: If Worksheets(1).Columns(i).Formula <> "" Then
you're looking at a collection of variants ... so I'm not clear as to ehat
this is trying to achieve. If you are testing to see if the column has data,
theen use the COUNTA() function

(2)

Dim TNum(), Term2, Iput, Iput2 As String
Dim j, k As Integer

is wrong...
in VBA, if you don't explicitly state the data type, the variant is the
default.
You have DIM, by default is this:
Dim TNum() As Variant, Term2 As Variant, Iput As Variant, Iput2 As String
Dim j As Variant, k As Integer

I assume that you want

Dim TNum() As String, Term2 As String, Iput As String, Iput2 As String
Dim j As Integer, k As Integer
 
Worksheets(1).Columns(i).Formula: A column does not have a formula. I assume
you are trying here to look in columns 2-7 for in the row you found in the
..Find method earlier in the code; if so I would do it this way (add a Dim
FoundCell as Range, FoundRow as Integer in the declarations):

Set FoundCell = Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByRows)
FoundRow = FoundCell.Row
....
Then, on the 'problem' line:
If Worksheets(1).Cells(FoundRow,i).Formula <>"" Then
....
Also, later in your code,
TNum(j) = Worksheets(1).Cells(FoundRow,i).Formula

Now a few things about the ReDim: ReDim will wipe out existing values held
in the array unless you ReDim Preserve. Also, if you use ReDim (1-6), it
does not index the array from 1 to 6; instead it does ReDim ("1 minus 6") and
you will get a 'subscript out of range' error for a subscript of -5)

I think that (with all taken into account) you will want it to read as
follows:
ReDim Preserve TNum(1 to j)
 
Okay, you guys helped me immensely, your suggestions work great, but I'm
having one more problem (that I sort of created for myself :rolleyes: ).
The problem is that I need to be able to output each of the values for
items that have less than 6 values. I'm only 50% successful. I
figured I needed a loop, so I declared an integer "l" and made this
loop:


Code:
--------------------
For l = 0 To j
Term2 = MsgBox("The values for this item are:" & vbCr & vbCr & _
TNum(j) & ",", vbYesNoCancel + vbDefaultButton2, "Values")
Next l
--------------------


What it does, however, is display the values one at a time in the
message box. Then, for lack of better words, doubles the amount of
values shown. As in, if the values in the cells it looks at are 150
and 1783, I'll get 4 message boxes, the first saying "150,", I'll hit a
button, the next says "1783,", hit the button, and two more will pop up,
both containing "1783,".

I'm just trying to make the loop display each of the values with commas
in between each. I'll have to figure a way to make it omit the comma
on the last value.


PHP code:
--------------------
Sub Search()
'

Dim TNum() As String, Term As String, Term2 As String, Iput As String, Iput2 As String
Dim j As Integer, k As Integer, l As Integer
Dim FoundCell As Range, FoundRow As Integer
j = 0

Iput = InputBox("Please enter item:") 'Input
Set FoundCell = Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByRows)
FoundRow = FoundCell.Row

For i = 2 To 7

If Worksheets(1).Cells(FoundRow, i).Value <> "" Then
j = j + 1

ReDim Preserve TNum(1 To j)
TNum(j) = Worksheets(1).Cells(FoundRow, i).Value

End If
Next i

For l = 0 To j
Term2 = MsgBox("The values for this item are:" & vbCr & vbCr & _
TNum(j) & ",", vbYesNoCancel + vbDefaultButton2, "Values")
Next l
 
Back
Top