If text, then select entire row + run makro

[raphagcwill]

Hi there,

would really appreciateyour help with the below.

I have found and changed to my needs the VBA i found here (excel - Filling any empty cells with the value above - Stack Overflow).

What it does is that i search on column N for the text "PPO". If true it copies the data on cells above.

The problem is that the macro only works when i select the row i want it to run... if I run the macro without selecting the row nothing happens

Is there any "easy" vba for selecting an entire row based on a text found on column N?


Sub copy_row_above()
Dim columnValues As Range, i As Long

Set columnValues = Selection

i = 1

For i = 1 To columnValues.rows.Count
If columnValues.Cells(i, 14).Value = "PPO" Then
columnValues.Cells(i, 3).Value = columnValues.Cells(i - 1, 3).Value
columnValues.Cells(i, 9).Value = columnValues.Cells(i - 1, 9).Value
columnValues.Cells(i, 10).Value = columnValues.Cells(i - 1, 10).Value
columnValues.Cells(i, 11).Value = columnValues.Cells(i - 1, 11).Value
columnValues.Cells(i, 12).Value = columnValues.Cells(i - 1, 12).Value

End If
Next

End Sub



Thank you so much in advance

Regards,

Raphael

 

[RCBricker]

Hi there,

would really appreciateyour help with the below.

I have found and changed to my needs the VBA i found here (excel - Filling any empty cells with the value above - Stack Overflow).

What it does is that i search on column N for the text "PPO". If true it copies the data on cells above.

The problem is that the macro only works when i select the row i want it to run... if I run the macro without selecting the row nothing happens

Is there any "easy" vba for selecting an entire row based on a text found on column N?


Sub copy_row_above()
Dim columnValues As Range, i As Long

Set columnValues = Selection

i = 1

For i = 1 To columnValues.rows.Count
If columnValues.Cells(i, 14).Value = "PPO" Then
columnValues.Cells(i, 3).Value = columnValues.Cells(i - 1, 3).Value
columnValues.Cells(i, 9).Value = columnValues.Cells(i - 1, 9).Value
columnValues.Cells(i, 10).Value = columnValues.Cells(i - 1, 10).Value
columnValues.Cells(i, 11).Value = columnValues.Cells(i - 1, 11).Value
columnValues.Cells(i, 12).Value = columnValues.Cells(i - 1, 12).Value

End If
Next

End Sub



Thank you so much in advance

Regards,

Raphael

try something like

Cells(i, 1).EntireRow.Select

 

[raphagcwill]

Hi,

Thanks for the suggestion...unfortunately it did not work.

i tried the following


Sub copy_row_above()

Dim columnValues As Range, i As Long

Set columnValues = Selection

i = 1


For i = 1 To columnValues.rows.Count

If columnValues.Cells(i, 14).Value = "PPO" Then
Cells(i, 1).EntireRow.Select
columnValues.Cells(i, 3).Value = columnValues.Cells(i - 1, 3).Value
columnValues.Cells(i, 9).Value = columnValues.Cells(i - 1, 9).Value
columnValues.Cells(i, 10).Value = columnValues.Cells(i - 1, 10).Value
columnValues.Cells(i, 11).Value = columnValues.Cells(i - 1, 11).Value
columnValues.Cells(i, 12).Value = columnValues.Cells(i - 1, 12).Value

End If
Next

End Sub

 

[RCBricker]

Hi,

Thanks for the suggestion...unfortunately it did not work.

i tried the following


Sub copy_row_above()

Dim columnValues As Range, i As Long

Set columnValues = Selection

i = 1


For i = 1 To columnValues.rows.Count

If columnValues.Cells(i, 14).Value = "PPO" Then
Cells(i, 1).EntireRow.Select
columnValues.Cells(i, 3).Value = columnValues.Cells(i - 1, 3).Value
columnValues.Cells(i, 9).Value = columnValues.Cells(i - 1, 9).Value
columnValues.Cells(i, 10).Value = columnValues.Cells(i - 1, 10).Value
columnValues.Cells(i, 11).Value = columnValues.Cells(i - 1, 11).Value
columnValues.Cells(i, 12).Value = columnValues.Cells(i - 1, 12).Value

End If
Next

End Sub

what did not work. Are you saying that it did not select the entire row? Did it throw an error? if so, which error?

 

[raphagcwill]

Sorry, should have been clearer.

No error...the macro runs but no cell is selected. And since no cell is selected the orginal code does not work..

The first cell is selcted only when i change to

Cells(i, 1).EntireRow.Select
If columnValues.Cells(i, 14).Value = "PPO" Then

But if I have it on the other way around is does not work.

 

[RCBricker]

Sorry, should have been clearer.

No error...the macro runs but no cell is selected. And since no cell is selected the orginal code does not work..

The first cell is selcted only when i change to

Cells(i, 1).EntireRow.Select
If columnValues.Cells(i, 14).Value = "PPO" Then

But if I have it on the other way around is does not work.

ok I used your code and the select works, the problem is you didn't change you code to do anything with the select.

What is it you want your code to do AFTER selecting the entire row?

 

[raphagcwill]

I would like it to copy the the data from the cells above, but only on columns C, I, J, K, and L.
The cells where i want the data to be copied is always blank.

The code i posted previouslly works flawlessly, men only when I select the row where my tex "PPO" is.

So that is why i thought that by finding and selecting the row the other code would run normally

Once agains, thanks for you time and effort

Mvh,
Raphael

 

[Akuini]

This line looks suspicious:

If columnValues.Cells(i, 14).Value = "PPO" Then
        Cells(i, 1).EntireRow.Select

columnValues is a range, so for example if columnValues is (A5:A10) then columnValues.Cells(1, 1) means A5 not A1.

 

[RCBricker]

I would like it to copy the the data from the cells above, but only on columns C, I, J, K, and L.
The cells where i want the data to be copied is always blank.

The code i posted previouslly works flawlessly, men only when I select the row where my tex "PPO" is.

So that is why i thought that by finding and selecting the row the other code would run normally

Once agains, thanks for you time and effort

Mvh,
Raphael

ok I don't think selecting the entire row is needed.

this will select the whole sheet and run your code till it reaches the last row that houses data. Most of this is your code. I cleaned it up a bit.

Sub copy_row_above()

 Dim columnValues As Range, i As Long
 
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    Set columnValues = ActiveSheet.Cells
    i = 1
    For i = 1 To columnValues.ROWS.Count
       If Cells(i, 14).Value = "PPO" Then
          Cells(i, 3).Value = Cells(i - 1, 3).Value
          Range(ActiveSheet.Cells(i, 9), ActiveSheet.Cells(i, 12)).Value = _
            Range(ActiveSheet.Cells(i - 1, 9), ActiveSheet.Cells(i - 1, 12)).Value
       End If
    Next
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
 End Sub

 

[raphagcwill]

OMG, OMG

it did work without any glicth. You are the man!!!!

Thanks a million for all the effort and commitment

Many Regards,
Raphael