VBA if cell = "text" then pull value from cell H if not pull cell value from G

[Legacy 362125]

Hi All

I am new to this forum and I am looking for some help if possible.

I have a worksheet which have VBA in it one of which references cell A2 and if that contains specific if it is true then pulls the value from cell H if not it pulls the value from cell G, I am also looking to copy the formula from cell J2 down to the last active cell

Here is the code - can anyone help ?

Sub Forecast()
Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("J2:J" & LastRowColumnA).Formula = IF(ISNUMBER(SERCH("Completed",A2)),H2,I2
End Sub

Thanks

 

[Fishboy]

Hi All

I am new to this forum and I am looking for some help if possible.

I have a worksheet which have VBA in it one of which references cell A2 and if that contains specific if it is true then pulls the value from cell H if not it pulls the value from cell G, I am also looking to copy the formula from cell J2 down to the last active cell

Here is the code - can anyone help ?

Sub Forecast()
Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("J2:J" & LastRowColumnA).Formula = IF(ISNUMBER(SERCH("Completed",A2)),H2,I2
End Sub

Thanks

Hi CraigieL, welcome to the boards.

If I am understanding what you are trying to do correctly, try out the following in a COPY of your workbook:

Sub CheckForTextValue()
' Defines variables
Dim Cell As Range, cRange As Range
    ' Defines LastRow as last row of data based on column A
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        ' Sets check range as A2 to the last row of A
        Set cRange = ActiveSheet.Range("A2:A" & LastRow)
            ' For each cell in the check range
            For Each Cell In cRange
                ' If the cell value contains "text" (cell can be case insensitive but must be in caps here) then...
                If UCase(Cell.Value) Like "*TEXT*" Then
                    ' Update column J of the cell row with the value from column H of the cell row
                    ActiveSheet.Range("J" & Cell.Row).Value = ActiveSheet.Range("H" & Cell.Row).Value
                ' Else if the cell value does not contain "text" then...
                Else
                    ' Update column J of the cell row with the value from column G of the cell row
                    ActiveSheet.Range("J" & Cell.Row).Value = ActiveSheet.Range("G" & Cell.Row).Value
                End If
            ' Check next cell in check range
            Next Cell
End Sub

 

[Legacy 362125]

Re: VBA if cell = "text" then pull value from cell H if not pull cell value from G (SOLVED

Hi CraigieL, welcome to the boards.

If I am understanding what you are trying to do correctly, try out the following in a COPY of your workbook:

Sub CheckForTextValue()
' Defines variables
Dim Cell As Range, cRange As Range
    ' Defines LastRow as last row of data based on column A
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        ' Sets check range as A2 to the last row of A
        Set cRange = ActiveSheet.Range("A2:A" & LastRow)
            ' For each cell in the check range
            For Each Cell In cRange
                ' If the cell value contains "text" (cell can be case insensitive but must be in caps here) then...
                If UCase(Cell.Value) Like "*TEXT*" Then
                    ' Update column J of the cell row with the value from column H of the cell row
                    ActiveSheet.Range("J" & Cell.Row).Value = ActiveSheet.Range("H" & Cell.Row).Value
                ' Else if the cell value does not contain "text" then...
                Else
                    ' Update column J of the cell row with the value from column G of the cell row
                    ActiveSheet.Range("J" & Cell.Row).Value = ActiveSheet.Range("G" & Cell.Row).Value
                End If
            ' Check next cell in check range
            Next Cell
End Sub

Thanks works like a dream, thanks again for your help

 

[Fishboy]

Re: VBA if cell = "text" then pull value from cell H if not pull cell value from G (SOLVED

Thanks works like a dream, thanks again for your help

Happy to help