Macro - Formula to loop through next Column excluding cell values of countblank = 1

[ffionnah]

Hia,
Thank you in advance for your help.

I have data from G3 through to BBI98 and there are many blanks within this range.

What I am trying to do is concat 2 numbers, starting at E3, while skipping cells with the value of CountBlank = 1 and looping to the next column. This concat formula would continue down through column E.

The formula is simply: =IF(COUNTBLANK(G3)=1,"",(CONCAT($G$1,G3)))


Once this reaches G98, I need it to loop to H3 and start all over again.

=IF(COUNTBLANK(H3)=1,"",(CONCAT($H$1,H3)))

Once this reaches H98, I need it to loop to I3 and start all over again.

=IF(COUNTBLANK(I3)=1,"",(CONCAT($I$1,I3)))

Etc all the way to BBI98.

So, what I am hoping to do is create a macro that will loop this and skip any cells that CountBlank = 1 (since they all have formulas within them as well), while still looping through row 98. This is because there could be data in all rows but 20,21,22,etc but I dont want this formula to just stop at the blanks and skip the rest of the potential data through row 98.

Here is a very small example:

​

[TABLE="width: 536"]
<tbody>[TR]
[TD][/TD]
[TD]

109​

[/TD]
[TD]

102​

[/TD]
[TD]

570​

[/TD]
[TD]

571​

[/TD]
[TD]

234​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

I​

[/TD]
[TD]

I​

[/TD]
[TD]

I​

[/TD]
[TD]

I​

[/TD]
[TD]

S​

[/TD]
[/TR]
[TR]
[TD]

BUS​

[/TD]
[TD]

1558​

[/TD]
[TD]

1558​

[/TD]
[TD]

1558​

[/TD]
[TD]

1558​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

BUS​

[/TD]
[TD]

1255​

[/TD]
[TD]

1255​

[/TD]
[TD]

1255​

[/TD]
[TD]

1255​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

CAN​

[/TD]
[TD]

1730​

[/TD]
[TD]

1730​

[/TD]
[TD]

1730​

[/TD]
[TD]

1730​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

CAR​

[/TD]
[TD]

1205​

[/TD]
[TD]

1205​

[/TD]
[TD]

1205​

[/TD]
[TD]

1205​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

HAM​

[/TD]
[TD]

1689​

[/TD]
[TD]

1689​

[/TD]
[TD]

1689​

[/TD]
[TD]

1689​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

HAR​

[/TD]
[TD]

1083​

[/TD]
[TD]

1083​

[/TD]
[TD]

1083​

[/TD]
[TD]

1083​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

HAR​

[/TD]
[TD]

1306​

[/TD]
[TD]

1306​

[/TD]
[TD]

1306​

[/TD]
[TD]

1306​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

IRB​

[/TD]
[TD]

1275​

[/TD]
[TD]

1275​

[/TD]
[TD]

1275​

[/TD]
[TD]

1275​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

KHA​

[/TD]
[TD]

1174​

[/TD]
[TD]

1174​

[/TD]
[TD]

1174​

[/TD]
[TD]

1174​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

MAI​

[/TD]
[TD]

1306​

[/TD]
[TD]

1306​

[/TD]
[TD]

1306​

[/TD]
[TD]

1306​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

MAY​

[/TD]
[TD]

1720​

[/TD]
[TD]

1720​

[/TD]
[TD]

1720​

[/TD]
[TD]

1720​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

MER​

[/TD]
[TD]

1760​

[/TD]
[TD]

1760​

[/TD]
[TD]

1760​

[/TD]
[TD]

1760​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

MOH​

[/TD]
[TD]

1336​

[/TD]
[TD]

1336​

[/TD]
[TD]

1336​

[/TD]
[TD]

1336​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

MUR​

[/TD]
[TD]

1265​

[/TD]
[TD]

1265​

[/TD]
[TD]

1265​

[/TD]
[TD]

1265​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

NOE​

[/TD]
[TD]

1972​

[/TD]
[TD]

1972​

[/TD]
[TD]

1972​

[/TD]
[TD]

1972​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

PAN​

[/TD]
[TD]

1669​

[/TD]
[TD]

1669​

[/TD]
[TD]

1669​

[/TD]
[TD]

1669​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

PAT​

[/TD]
[TD]

1164​

[/TD]
[TD]

1164​

[/TD]
[TD]

1164​

[/TD]
[TD]

1164​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

POW​

[/TD]
[TD]

1811​

[/TD]
[TD]

1811​

[/TD]
[TD]

1811​

[/TD]
[TD]

1811​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

RIV​

[/TD]
[TD]

1619​

[/TD]
[TD]

1619​

[/TD]
[TD]

1619​

[/TD]
[TD]

1619​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

TOR​

[/TD]
[TD]

1568​

[/TD]
[TD]

1568​

[/TD]
[TD]

1568​

[/TD]
[TD]

1568​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

LAC​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

AND​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

COO​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

DEM​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

GOD​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

MAR​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

MAY​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

OFF​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

SUC​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

WIL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

COZ​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

KLA​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

PRI​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

SIL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

THI​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

VAS​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

SEN​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

SHA​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

KAR​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

VEG​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

WHI​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

BAR​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

REZ​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

ROG​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

WAR​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

DUN​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

LAM​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

HEN​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

GAR​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

HAL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

LEE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

JON​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

KAP​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

KAS​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

KEN​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

LEE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

LOC​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

LON​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

LOW​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

BRA​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

MIZ​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

RAN​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

ALT​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

BUR​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

GON​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1295​

[/TD]
[/TR]
[TR]
[TD]

ROB​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

PEL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

BOL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1295​

[/TD]
[/TR]
[TR]
[TD]

RIS​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1851​

[/TD]
[/TR]
[TR]
[TD]

SCH​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1609​

[/TD]
[/TR]
[TR]
[TD]

THO​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1083​

[/TD]
[/TR]
[TR]
[TD]

WAL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1083​

[/TD]
[/TR]
[TR]
[TD]

WIL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1669​

[/TD]
[/TR]
[TR]
[TD]

WEC​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

BAR​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

RIC​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

ART​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

BHA​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

BLI​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

DOE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

GOR​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

KRI​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

LAN​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

LAR​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

MER​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

MIL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

OUH​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

PAR​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

TAL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

BER​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

ELL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

AMA​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

CHI​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

PAT​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

SHA​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

PUG​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is this possible!? Also, is it possible to have the formula not calculate the CountBlank = 1 value cells so there are not blank cells within the concat Column E?

Thank you!

 

[ffionnah]

So far, all i have is this code which doesnt circulate through even the first column nor does it loop

Sub ConcTrial()


   Range(ActiveCell.Offset(2, 4), ActiveCell.End(xlDown).Offset(2, 4)).FormulaR1C1 = "=IF(COUNTBLANK(RC[2])=1,"""",(CONCAT(R1C7,RC[2])))"


End Sub

 

[ffionnah]

I tried something like this but I cannot seem to get it to actually loop through the next column, while still pasting the formula into column E.

Sub ConcTrial()
Dim LastRowColumnE As Long
LastRowColumnE = Cells(Rows.Count, 7).End(xlUp).Row
For Y = 7 To Cells(3, Columns.Count).End(xlToRight).Column


 x = 3


If Cells(x, Y) <> 1 Then
   Range("E3:E" & LastRowColumnE).FormulaR1C1 = "=IF(COUNTBLANK(RC[2])=1,"""",(CONCAT(R1C7,RC[2])))"
   End If
   Do Until Cells(x, Y).Value = Blank
   x = x + 1
   Loop
   
Next Y


End Sub

 

[ffionnah]

I have changed some of the data around a little and didn't want to confuse anyone who may potentially try to help. Please disregard the above attempts, as my other post was locked.




I am trying to create a macro to paste a concatenation formula into a new column for the range of Columns G3 to BBI98. The current code I have is looping, but it is not continuously pasting this concatenation formula down through the new column.

Is there a way for the concatenation formula to paste the info for the first group G3 to G98 in E3 to E98 and then loop back to H3 to H98 and paste this concatenation formula in E99 to E198... etc?

Thank you, in advance, for your help!

Sub ConcTrial()
Dim LastRowColumnE As Long
Dim RL As Long, Cl As Integer
RL = Cells(3, 7)

LastRowColumnE = Cells(Rows.Count, 7).End(xlUp).Row
For i = 7 To 1407

x = 3
If Cells(x, i) <> 1 Then
   Range("E3:E" & LastRowColumnE).FormulaR1C1 = "=IF(COUNTBLANK(RC[2])=1,"""",(CONCAT(R1C7,RC[2])))"
End If

 Next i


End Sub

 

[Fluff]

Closed at Op's request