CODEWITHSUNDEEP
vbaexcel
Jasmita Sagi
Jasmita Sagi

Reputation: 47

String in Array of numbers gives error

Sub highlight()
   Dim w As Workbook
   Dim sh As Worksheet
   Dim x As Integer
   Dim rn As Range
   Dim k As Long
   Dim number As Variant

   number = Array(9811, 7849)


   Set w = ThisWorkbook


   Set sh = w.Worksheets("Sheet1")
   sh.Select
   Cells.Find("hello").Select
   ActiveCell.Offset(1, 0).Select
   Set rn = sh.UsedRange
   k = rn.Rows.Count + rn.Row - 1
   For x = 1 To k
      For j = 0 To UBound(number)
         If ActiveCell.Value <> number(j) Then
            Selection.Interior.Color = vbYellow
         Else
            Selection.Interior.ColorIndex = xlNone
            Exit For
         End If

      Next j
      ActiveCell.Offset(1, 0).Select 'moves activecell down one row.
   Next x

End Sub

The above code works completely fine.
Now, when I send the same numbers (9811,7849) from my GUI to the below vba code,they are stored as phm="9811,7849". So Array("9811,7849") gives me incorrect result.Any ideas how to make it work correctly?\

sub highlight(phm as variant)
Dim w As Workbook
Dim sh As Worksheet
Dim x As Integer
Dim rn As Range
Dim k As Long
Dim number As Variant


number = Array(phm)

Upvotes: 1

Views: 66

Answers (1)

Uri Goren
Uri Goren

Reputation: 13690

Array("9811,7849") is an array with one String element: "9811,7849"

And Array(9811,7849) is an array with two elements: 9811 and 7849

You should take a look at the Split() function

number=Split(phm,",")

And if you need number as Integer, apply also CInt():

Dim number() As Integer
phm=Split("9811,7849",",")
ReDim number(LBound(phm) To UBound(phm)) As Integer
For i=LBound(phm) To UBound(phm)
  number(i)=CInt(phm(i))
Next i

Edit

You requested in the comments to add this snippet to your subroutine:

Sub highlight(ByVal phm As String)
   Dim w As Workbook
   Dim sh As Worksheet
   Dim x As Integer
   Dim rn As Range
   Dim k As Long
   Dim number() As String


   number = Split(phm, ",")


   Set w = ThisWorkbook


   Set sh = w.Worksheets("Sheet1")
   sh.Select
   Cells.Find("hello").Select
   ActiveCell.Offset(1, 0).Select
   Set rn = sh.UsedRange
   k = rn.Rows.Count + rn.Row - 1
   For x = 1 To k
      For j = 0 To UBound(number)
         If ActiveCell.Value <> number(j) Then
            Selection.Interior.Color = vbYellow
         Else
            Selection.Interior.ColorIndex = xlNone
            Exit For
         End If

      Next j
      ActiveCell.Offset(1, 0).Select 'moves activecell down one row.
   Next x

End Sub

Upvotes: 2

Related Questions