Help with another looping macro as long as there is a value on column A.

[Coyotex3]

Hello All, another thread about loops that I hope you guys can help me with. Ideally, I would like to troubleshoot this in order to understand what I’m missing and how to go about finding the steps to fixing the macro.



So, I’m exporting a certain file into Excel. Row 1 will always be the headers. I want “B2” to copy what is on “E2” and then find the next range. Here is the code I have so far.

Sub Macro14()



Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[3]"

ActiveCell.Offset(1, 0).Range("A1").Select

Selection.End(xlDown).Select

Selection.End(xlDown).Select

ActiveCell.Offset(-1, 0).Range("A1").Select

End Sub



The problem is that on today’s sheet on “B69” there is nothing beneath it therefore Excel is bypassing that row and is stopping in the row prior to it reaching a “non-blank” cell. I want Excel to copy whatever is on “E2” into “B2” as
as long as “A1” is filled.



I know the macro I have is nowhere close to doing that yet. I’m trying to put it all together little by little with your help and the help of the macro recorder.

 

[Tom Urtis]

I was almost with you until this you wrote came out of nowhere:
"as long as “A1” is filled."

Is cell A1 absolute or relative with regards to the other rows, and if it is relative, why start on row 2 for the data but row 1 (cell A1) being a determinant.

Also, what does "blank" mean to you. Is there nothing (nothing means nothing) in the cell, or is there a formula that sometimes returns a null string.

Is it formulas or constant static values in column B.
Is it formulas or constant static values in column E.

 

[Coyotex3]

I was almost with you until this you wrote came out of nowhere:
"as long as “A1” is filled."

Is cell A1 absolute or relative with regards to the other rows, and if it is relative, why start on row 2 for the data but row 1 (cell A1) being a determinant.

Also, what does "blank" mean to you. Is there nothing (nothing means nothing) in the cell, or is there a formula that sometimes returns a null string.

Is it formulas or constant static values in column B.
Is it formulas or constant static values in column E.

Hey Tom, thank you for your reply and I'm sorry that my explanation isn't very good. I started learning about VBA and Macros on Sunday.

Hopefully this helps explain it better. Ideally I would like for this macro to go from B14 to "B17" and copy whatever is on E17 and then jump to B20. My bump in the row is the fact that excel is bypassing "B17" I want it to stop if possible.

 

[Tom Urtis]

If that is what you want, why is it that USA is in column B but "Something" and "Almost There" are in column E.

Based on the picture, I know the range is dynamic, so for this example...
Is it that you want the word "Total" to go into cell B15 and B17 and B36?
You want "Almost There" to go into cell B20?

And what does "copy whatever" mean? If the text is bold in column E would it be bold in column B?

Also, I asked you questions you did not answer, so please answer them. Repeating my questions:
Is it formulas or constant static values in column B.
Is it formulas or constant static values in column E.

 

[Coyotex3]

If that is what you want, why is it that USA is in column B but "Something" and "Almost There" are in column E.

Based on the picture, I know the range is dynamic, so for this example...
Is it that you want the word "Total" to go into cell B15 and B17 and B36?
You want "Almost There" to go into cell B20?

And what does "copy whatever" mean? If the text is bold in column E would it be bold in column B?

Also, I asked you questions you did not answer, so please answer them. Repeating my questions:
Is it formulas or constant static values in column B.
Is it formulas or constant static values in column E.

Hey Tom, I thought I edited the post to answer those questions so my apologies. What I attached is just a template as to how the data is displayed. No formulas involved just constant static values.

For Cell "B2" I want it to display "Lets do it."
for Cell "E17" I want it to display "Total"
for Cell "E20" I want it to display "Almost There."

I should have wrote something else on cell "E17" to avoid confusion. if cell "E17" had something else in it, I would like to be displayed on "B17"

Trying to understand exactly what I'm missing. Ideally later on, once I copy the values From "E2" to "B2" or From "E17" to "B17" I will copy those values down in order to autofill prior to jumping to the next range. I'm trying to piece things together one by one instead of asking the forum to just give me the answer.

 

[Tom Urtis]

Try this for starters.

Sub FillInB()
Dim lngLastRow&, cell As Range, rngEvalRange As Range
lngLastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rngEvalRange = Range("B2:B" & lngLastRow)
If WorksheetFunction.CountBlank(rngEvalRange) = 0 Then Exit Sub
Application.ScreenUpdating = False
For Each cell In rngEvalRange.SpecialCells(4)
If Len(Cells(cell.Row, 1)) > 0 Then cell.Value = Cells(cell.Row, 5).Value
Next cell
Set rngEvalRange = Nothing
Application.ScreenUpdating = True
End Sub

 

[Coyotex3]

Try this for starters.

Sub FillInB()
Dim lngLastRow&, cell As Range, rngEvalRange As Range
lngLastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rngEvalRange = Range("B2:B" & lngLastRow)
If WorksheetFunction.CountBlank(rngEvalRange) = 0 Then Exit Sub
Application.ScreenUpdating = False
For Each cell In rngEvalRange.SpecialCells(4)
If Len(Cells(cell.Row, 1)) > 0 Then cell.Value = Cells(cell.Row, 5).Value
Next cell
Set rngEvalRange = Nothing
Application.ScreenUpdating = True
End Sub

This is perfect. If possible, could you tell me exactly what is in this sheet that prevents it from bypassing row 17?

 

[Tom Urtis]

This line here...
For Each cell In rngEvalRange.SpecialCells(4)
...says that the only cells you care about in column B are cells that are empty, and therefore are the only cells being potentially filled.

This line after that...
If Len(Cells(cell.Row, 1)) > 0 Then cell.Value = Cells(cell.Row, 5).Value
...says that if there is something in column A, then copy whatever is in column E of that row to column B of that row.

In this way, only the relatively few cells that are empty in column B are evaluated, which speeds up the macro by not even bothering with occupied cells in column B.

 

[Coyotex3]

This line here...
For Each cell In rngEvalRange.SpecialCells(4)
...says that the only cells you care about in column B are cells that are empty, and therefore are the only cells being potentially filled.

This line after that...
If Len(Cells(cell.Row, 1)) > 0 Then cell.Value = Cells(cell.Row, 5).Value
...says that if there is something in column A, then copy whatever is in column E of that row to column B of that row.

In this way, only the relatively few cells that are empty in column B are evaluated, which speeds up the macro by not even bothering with occupied cells in column B.

Thank you so much! This is bonkers. The macro I had (used the macro recorder) was never going to work for me, and this one definitely works. I would love to be able to understand what is going on behind the scenes though. I just discovered VBA/Macros its amazing.