2 replies to this topic

[libran]

hi all,

I need to write a VBA code which will search data in 'Bold' from a list. It should be in dynamic array since the data will be updated constantly. I met many problem with this....attached is the data i need to work on...thanx a lot

Sub DynamicArray()

Dim Device() As String ' declares a dynamic array variable
Dim iCount As Integer
Dim Max As Integer
    Worksheets("Proto & Other Experiment").Activate
    ActiveSheet.Range("B2").EntireColumn.Select
'    Device = Selection.FindText(True, LookIn:=xlsPattern)
    Max = Worksheets("Proto & Other Experiment").Device.Count  ' finds the maximum array size
    ReDim Device(1 To Max) ' declares the array variable with the necessary size
    For iCount = 1 To Max
        Device(iCount) = Worksheets("Proto & Other Experiment").Device(iCount)
    Next iCount
    Erase Device() ' deletes the varible contents, free some memory
End Sub

Attached Files

•  help.doc   80.5K   43 downloads

[libran]

hey, thanx for the help...i hv write out the code as below...but keep having error in this line [For arr = 0 To Device(arrAddr).Size]

Ani cant figure out the error...pls help me on tt..thanx

Sub DynamicArray()

Dim Device() As String    ' declares a dynamic array variable
Dim iCount As Integer
Dim Max As Integer

Max = 0
   Worksheets("Proto & Other Experiment").Activate
   ActiveSheet.Range("B2").EntireColumn.Select


   For row = 2 To 200
    If Cells(row, 2).Font.Bold = True Then
    Max = Max + 1
    End If
   Next row
   
   ReDim Device(Max)
   arrAddr = 0
   
   For row = 2 To 200
    If Cells(row, 2).Font.Bold = True Then
    Device(arrAddr) = Cells(row, 2).Value
    arrAddr = arrAddr + 1
    End If
   Next row


   For arr = 0 To Device(arrAddr).Size 'Size of the array
   ODBCConn (Device(arrAddr))
   Next arr
  End Sub