none
vba syntax RRS feed

  • Question

  • Hi,

    I am trying to change the value of 2 or more cells in a range based on the value of one cell in a range.

    Has anyone done this?

    I cannot find the correct syntax.

    Here's what i have so far:

    Sub GLcode()

    If Sheet1("G12:G29") = "GL" Then
       Sheet1("J12:J29") = "63200"
       Sheet1("K12:J29") = "810 Estimating"

    End If

    End Sub

    any help would be appreciated.

    thanks!

        
    Monday, November 23, 2015 8:34 PM

Answers

  • I'm not sure I understand, but does this do what you want?

    Sub GLcode()
        Dim r As Long
        Application.ScreenUpdating = False
        With Worksheets("Sheet1")
            For r = 12 To 29
                If .Range("G" & r).Value = "GL" Then
                    .Range("J" & r).Value = "63200"
                    .Range("K" & r).Value = "810 Estimating"
                End If
            Next r
        End With
        Application.ScreenUpdating = True
    End Sub

    If you want the value in column J to be the number 63200 instead of the text string "63200", change the line

                    .Range("J" & r).Value = "63200"

    to

                    .Range("J" & r).Value = 63200


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 23, 2015 9:46 PM