VBA - multiple conditions for each cell

Question

I'm trying to solve this code's issue, which I can't run:

'========================================================================
' CHECKS IF MARKET SECTOR IS EMPTY (FOR LEDGER)
'========================================================================

Private Sub Fill_MarketSector()

Dim LastRow As Long
Dim rng As Range, C As Range

With Worksheets("Ready to upload") ' <-- here should be the Sheet's name
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' last row in column A
    Set rng = .Range("A2:A" & LastRow) ' set the dynamic range to be searched
    Set rng2 = .Range("F2:F" & LastRow)

    ' loop through all cells in column A and column F
    For Each C In rng and For Each C in rng2
        If rng.C.Value = "Ledger" and rng2.C.value IsEmpty Then
            C.Value = "599" ' use offset to put the formula on column "L"
        End If
    Next C
End With

End Sub

The code should check if the column A contains word "Ledger" and column F is empty, then it should put into column F "599". It should always check to the very last row. Could you help me, please?

Thanks a lot!

re: '\*'should put into column F "599"'* - Column F or column L? Your narrative conflicts with the comment in your code. — , Jun 19 '17 at 14:17
What error are you getting? Provide information on where you are stuck. — PankajR, Jun 19 '17 at 14:20
You are right, I had a discrepancy between comments and code. It should put "599" into column F. I'm getting syntax error and this part of code has been highlighted: For Each C In rng and For Each C in rng2 If rng.C.Value = "Ledger" and rng2.C.value IsEmpty Then — Srpic, Jun 20 '17 at 06:38

score 0 · Accepted Answer · answered Jun 19 '17 at 14:13

0

You can access the accompanying cells in column F by looping through the cells in column A and using .Offset for column F then offset again to put the value in column L.

' loop through all cells in column A and column F
For Each C In rng
    If LCase(C.Value) = "ledger" and IsEmpty(C.Offset(0, 5) Then
        C.Offset(0, 11) = 599  'use offset to put the number on column "L"
    End If
Next C

answered Jun 19 '17 at 14:13

Thanks man! It works smoothly. There was just missing separator behind IsEmpty(C.Offset(0, 5)). Thank you very much! – Srpic Jun 20 '17 at 06:41
Unfortunately, it works only sometimes, for the most of time, it does nothing. Do you have an idea why, please? – Srpic Jun 20 '17 at 06:51
*'it works only sometimes'* - this is neither a valid error code nor a valid description of the error. Neither is this - *'for the most of time, it does nothing'*. Supply sample data after reading [mcve]. – Jun 20 '17 at 07:10

VBA - multiple conditions for each cell

1 Answers1