• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Enter a value in a blank column based on values in other column using VBA

Dear Experts:

I got a huge data list with column headers. And now I would like to perform the following tasks using a VBA macro:

The macro is to step to all the data records (row by row) and ...

check whether there are data records which have got the value '99' in Column C and 'not available' in Column G. In these cases the macro is to enter the value 'to be reviewed' in Column 'H' for that data record. Column H is a blank column

I know I could perform this action by using filtering but I wonder how the VBA solution would look like.

thank you very much in advance for your professional support.

Regards, Andreas
Andreas Hermle
Andreas Hermle
1 Solution
Public Sub FlagReviews()

    Dim rngCodes As Range
    Dim rngText As Range
    Dim wsThis As Worksheet
    Dim i As Long
    Set wsThis = ActiveSheet
    Dim vCodes As Variant
    Dim vText As Variant
    Dim vMessage As Variant
    Set rngCodes = wsThis.Range(wsThis.Cells(1, 3), wsThis.Cells(wsThis.Rows.Count, 3).End(xlUp))
    Set rngText = wsThis.Range(wsThis.Cells(1, 7), wsThis.Cells(wsThis.Rows.Count, 7).End(xlUp))
    ' check that both columns have the same number of rows
    If rngCodes.Rows.Count > rngText.Rows.Count Then
        Set rngText = rngText.Resize(rngCodes.Rows.Count, 1)
        Set rngCodes = rngCodes.Resize(rngText.Rows.Count, 1)
    End If

    ' there may be no data
    If rngCodes.Rows.Count = 1 Then Exit Sub

    vCodes = rngCodes.Value
    vText = rngText.Value
    ' set the message array equal to the others and get the header value if any
    ReDim vMessage(1 To UBound(vCodes, 1), 1 To 1)
    vMessage(1, 1) = wsThis.Cells(1, 8).Value
    ' start from 2 as we have headers
    For i = 2 To UBound(vCodes, 1)
        If (vCodes(i, 1) = 99) And (vText(i, 1) = "not available") Then
            vMessage(i, 1) = "to be reviewed"
        End If
    Next i
    ' put the messages onto the sheet
    wsThis.Cells(1, 8).Resize(UBound(vMessage, 1), 1).Value = vMessage

End Sub

Open in new window

I've done this in what may look rather a convoluted way using variant arrays to hold the data.  I did this because you said your list is very large, and this will run much quicker in that case than a solution that loops through row by row on the sheet.
Andreas HermleTeam leaderAuthor Commented:
Hi Andrew,

GREAT! Exactly what I wanted. Thank you very much for your swift and professional support. I really appreciate it.

Regards, Andreas
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now