Insert blank column after finding cell value

[B-radK]

Hello team,

I would like to know a vba code to find cell values, then insert a blank columns immediately after those cells.

For example, find a cell called "Bank" then insert a blank column afterwards. At the same time, find cells with "Transaction" and "Account" and insert blank columns after those too.

Any help would be greatly appreciated.

Thanks,
Brad.

 

[Peter_SSs]

find a cell called "Bank" then insert a blank column afterwards. At the same time, find cells with "Transaction" and "Account" a

1. Will those values only be found once each on the sheet?

2. Where will they be found (eg 'always in row 1' or 'they could be anywhere' or ...)?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[B-radK]

Hello,

Thanks, I will update my details. I’m using Office 365.

Yes, those values will only occur once in the sheet.

The values will always be in Row 4.

Thanks,
Brad

 

[Peter_SSs]

I will update my details.

Thanks for getting that done & the additional information.

Give this a try with a copy of your workbook.

Sub Insert_Columns()
  Dim AllHdrs As Variant, Hdr As Variant
  Dim rFound As Range
  
  AllHdrs = Split("Bank|Transaction|Account", "|")
  For Each Hdr In AllHdrs
    Set rFound = Rows(4).Find(What:=Hdr, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If Not rFound Is Nothing Then Columns(rFound.Column + 1).Insert
  Next Hdr
End Sub

 

[B-radK]

Hello Peter,

Thank you very much, it works very well.

I did try it with dates, however it didn't work. It did work when I changed the date format to general. I included in the code to add a new row above the date column, copy and paste the date, change the format to general, execute your code, then hide the row. A bit of a work around, but it's fine.

This is what I tried, but obviously failed:

AllHdrs = Split("31-Mar-20|30-Apr-20|30-Jun-20", "|")

Thanks again, Peter, I really appreciate it.

Brad.

 

[Peter_SSs]

Thank you very much, it works very well.

You're welcome.

I did try it with dates, however it didn't work.

That is because Dates are actually Numbers, not Text like "31-Mar-20"
For example, 31-Mar-2020 is actually 43921

So, for actual dates, try like this

Sub Insert_Columns_Dates()
  Dim AllHdrs As Variant, Hdr As Variant
  Dim rFound As Range
  
  AllHdrs = Split("31-Mar-20|30-Apr-20|30-Jun-20", "|")
  For Each Hdr In AllHdrs
    Set rFound = Rows(4).Find(What:=DateValue(Hdr), LookIn:=xlValues, LookAt:=xlWhole)
    If Not rFound Is Nothing Then Columns(rFound.Column + 1).Insert
  Next Hdr
End Sub

 

[B-radK]

G'day Peter,

That didn't work unfortunately mate. My work-around is working just fine though.

I tried swapping to "30/04/2020|31/05/2020|30/06/2020" as this is what's in the formula bar when I click on "30-Apr-20" but it didn't work.

I'm happy to keep trying, but I'm happy with my work around.

Thanks again,
Brad.

 

[Peter_SSs]

That didn't work unfortunately

Suppose one of your heading dates is in cell D4, what does this formula return if you place it in a vacant cell?

=ISNUMBER(D4)

IF that formula returns True, then what does this formula return?
=INT(D4)=D4

 

[B-radK]

Hello Peter,

Terribly sorry for the delay. Preparing to work at home with the COVID-19 situation.

They both return TRUE.

Thanks,
Brad.

 

[Peter_SSs]

They both return TRUE.

Then I don't know why the code from post # 6 does not work.

Here is my sheet before the code is run

20 04 01.xlsm
	A	B	C	D	E	F	G	H	I	J
3
4		31/01/2020	29/02/2020	31/03/2020	30/04/2020	31/05/2020	30/06/2020	31/07/2020	31/08/2020	30/09/2020
5		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
6		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
Insert Columns (3)

Cell Formulas
Range	Formula
B5:J5	B5	=ISNUMBER(B4)
B6:J6	B6	=INT(B4)=B4

and here it is after running the post # 6 code

20 04 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
3
4		31/01/2020	29/02/2020	31/03/2020		30/04/2020		31/05/2020	30/06/2020		31/07/2020	31/08/2020	30/09/2020
5		TRUE	TRUE	TRUE		TRUE		TRUE	TRUE		TRUE	TRUE	TRUE
6		TRUE	TRUE	TRUE		TRUE		TRUE	TRUE		TRUE	TRUE	TRUE
Insert Columns (3)

Cell Formulas
Range	Formula
B5:D5,F5,K5:M5,H5:I5	B5	=ISNUMBER(B4)
B6:D6,F6,K6:M6,H6:I6	B6	=INT(B4)=B4