CODEWITHSUNDEEP
excelvba
FotoDJ
FotoDJ

Reputation: 351

VBA Loop on condition: use value of column B only if Column A in this row is empty

I am working on vba code which gets certain set of data from website, running through values from column B, I would like to add condition that if column A has any text or number, the code should skip that row. In other words code would only use values from column B if column A is empty in the same row. Here is the code:

   Dim Cel As Range, ms As Worksheet, dom As HTMLDocument

Set ms = Sheets("Scramble")
Const searchUrl = "http://www.scramble.nl/index.php?
option=com_mildb&view=search"

For Each Cel In ms.Range("B2:B" & ms.Range("B" & 
Rows.Count).End(xlUp).row).SpecialCells(2)

    Set dom = New HTMLDocument


    With CreateObject("winhttp.winhttprequest.5.1")
        .Open "POST", searchUrl, False
        .setRequestHeader "Content-Type", "application/x-www-form-
urlencoded"
        .send "Itemid=60&af=usaf&serial=" & Cel & 
"&sbm=Search&code=&searchtype=&unit=&cn="
        dom.body.innerHTML = .responseText
    End With

    On Error Resume Next

    Cel.Offset(, 2) = dom.getElementsByClassName("rowBord")(0).Cells(1).innerText 'Code
    Cel.Offset(, -1) = dom.getElementsByClassName("rowBord")(0).Cells(2).innerText 'Type
    Cel.Offset(, 11) = dom.getElementsByClassName("rowBord")(0).Cells(5).innerText 'Status
    Cel.Offset(, 12) = dom.getElementsByClassName("rowBord")(0).Cells(7).innerText 'Last

   Next

End Sub

Upvotes: 1

Views: 292

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37460

Simple If statement is what you need.

'first check, if A cell in i-th row is empty
If Not Cells(i, 1).Value = "" Then
    'copy from B column
End If

Also this range can be specified in simplier way:

Range("B2:B" & ms.Range("B" & Rows.Count).End(xlUp).row)

Like this

Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))

But what you will need is to loop through all these cells and check condition I mentioned for every row separately, something like this:

For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
    'first check, if A cell in i-th row is empty
    If Not Cells(i, 1).Value = "" Then
        'copy from B column
    End If
Next i

Upvotes: 1

Sixthsense
Sixthsense

Reputation: 1975

Replace these lines

    Cel.Offset(, 2) = dom.getElementsByClassName("rowBord")(0).Cells(1).innerText 'Code
Cel.Offset(, -1) = dom.getElementsByClassName("rowBord")(0).Cells(2).innerText 'Type
Cel.Offset(, 11) = dom.getElementsByClassName("rowBord")(0).Cells(5).innerText 'Status
Cel.Offset(, 12) = dom.getElementsByClassName("rowBord")(0).Cells(7).innerText 'Last

With

With Cel
    If .Offset(, -1).Value = "" Then
        .Offset(, 2) = dom.getElementsByClassName("rowBord")(0).Cells(1).innerText 'Code
        .Offset(, -1) = dom.getElementsByClassName("rowBord")(0).Cells(2).innerText 'Type
        .Offset(, 11) = dom.getElementsByClassName("rowBord")(0).Cells(5).innerText 'Status
        .Offset(, 12) = dom.getElementsByClassName("rowBord")(0).Cells(7).innerText 'Last
    End If
End With

Upvotes: 1

Related Questions