CODEWITHSUNDEEP
excelvba
Tarun Aryan
Tarun Aryan

Reputation: 25

How to use range of column found using array as input and compare the range to a number to get output as "true" or "false" in a different column?

In the below code I found the column number of the values in the array. How to use that range to compare values to a with a variable?

Public Sub JpFee()
        Dim rng As Range, rws As Long, w As Long, Str As Variant, Count As Integer, x(20) As Integer, Lt() As Variant, ht As Variant, Wdt As Variant, W8t As Variant
        Dim row1, i, y As Integer       

        row1 = ActiveSheet.UsedRange.Rows.Count

        MsgBox row1

        Str = Array("length (cm)", "width (cm)", "height (cm)", "unit weight(kg)", "surface area L+W+H", "size")
        For w = LBound(Str) To UBound(Str)
        Set rng = Rows("1:1").Find(What:=Str(w), LookAt:=xlWhole, MatchCase:=False)
        x(i) = rng.Column
        i = i + 1
        MsgBox rng.Column

        Next w

    End Sub

Upvotes: 0

Views: 46

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

I would use a collection for something like this. You could also use a dictionary. (Whichever you prefer).

Here's an example of using a collection with what you're trying to do:

Sub tgr()

    Dim ws As Worksheet
    Dim HeaderCell As Range
    Dim cHeaders As Collection

    Set ws = ActiveWorkbook.ActiveSheet
    Set cHeaders = New Collection

    On Error Resume Next    'Collections will error if there are duplicates, this prevents that error

    'Loop through each headercell
    For Each HeaderCell In ws.Range("A1").CurrentRegion.Resize(1).Cells
        'If the headercell contains text, add its column number to the collection and set the key to the headertext
        If Len(HeaderCell.Text) > 0 Then cHeaders.Add HeaderCell.Column, CStr(HeaderCell.Text)
    Next HeaderCell 'Advance the loop
    On Error GoTo 0         'Clear the On Error Resume Next condition

    'Now you can get the column number of a known header by referencing the header in your collection
    'This will show the column number of the header "size"
    'Note that the header does not need to be case sensitive, but must be the exact text
    MsgBox cHeaders("size")

    'So if you want to put in a value in the bottom row of a column:
    ws.Cells(ws.Rows.Count, cHeaders("size")).End(xlUp).Offset(1).Value = 2

End Sub

Upvotes: 1

Related Questions