• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need a quick macro, please?!- find text, insert cols, fill with numbers

T

thereseexceljakubiak

New Member
Hi all!


So, I have a wksht with a bunch of columns.

[pre]
Code: 
Col A- IDS
Col B- A 123
Col C- B 123
Col D- C 123
Col E- A 345.3
Col F- B 345.3
Col G- C 345.3
Col H- A 05.1
Col I- B 05.1
Col J- C 05.1
etc.
[/pre]
so, what i basically need is to find where the column header contains "A" to insert a column to the LEFT and fill that column with the numbers next to "A".. numbers/digits vary in length. Loop this for all columns that contain "A"


Numbers of Columns vary each day.


THANKS IN ADVANCE!!!
 
Hi, therese!

As per your example you'd be adding columns before (previous names) columns B, E and H. But what do you mean with "fill that column with the number next to "A"? Let us take column B, you add a new column before B, and you fill which cells with the value 123 (that was in the header of previous column B, now C?

Regards!
 
Hi SirJB7! I hear a "Welcome Back" is in order?! :)


So, yes- a new column would be inserted before columns B, E & H. Then I would like column B to be filled with "123" and column E to be filled with "345.3" and Column H with "05.1"....
 
Hi, therese!

Thanks, everything's going as scheduled now :)

I asked which cells of inserted columns should be filled with the value next to first letter of header. What I tried to mean is how many rows, starting at and ending at?

Regards!
 
Your last 3 posts were pretty confusing. Could you post a small example of what you want the end result to look? You can post it just like you did in your original post. I think it's getting confusing as to what you want in the columns, and what all you want copied.

If you just want to insert a new column, and then copy over the cells in rows 2:200, this would work, but I don't think that is quite what you want.

[pre]
Code: 
Sub FillACols()
Dim lastCol As Long
Application.ScreenUpdating = True
With ActiveSheet
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

For i = lastCol To 1 Step -1
With Cells(1, i)
If InStr(1, .Value, "A") > 0 Then
.EntireColumn.Insert
Range(Cells(2, i + 1), Cells(200, i + 1)).Copy Range(Cells(2, i), Cells(200, i))
End If
End With
Next i

Application.ScreenUpdating = True
End Sub
[/pre]
 
Hi, therese!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/need%20a%20quick%20macro%2C%20please_%21-%20find%20text%2C%20insert%20cols%2C%20fill%20with%20numbers%20%28for%20therese%20at%20chandoo.org%29.xlsm


The macro to be run is this:

-----

[pre]
Code: 
Option Explicit

Sub AddColumnsBeforeAInHeader()
' constants
Const ksKey = "a"
Const ksTitle = "Title "
' declarations
Dim I As Long, J As Long, A As String, B As String
Dim lColumn As Long, lColumnARows As Long
' start
With ActiveSheet
' header
lColumn = 1
' column A rows
If .Cells(.Rows.Count, 1).Value = "" Then
lColumnARows = .Cells(.Rows.Count, 1).End(xlUp).Row
Else
lColumnARows = .Rows.Count
End If
' cycle
Do Until .Cells(1, lColumn).Value = ""
A = .Cells(1, lColumn).Value
B = Trim$(Right$(A, Len(A) - InStr(A, " ")))
' check for key
If LCase(Left$(A, Len(ksKey))) = ksKey Then
' insert column
Columns(lColumn).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
' fill new column
.Cells(1, lColumn).Value = ksTitle & .Cells(lColumn + 1, 1).Value
For I = 2 To lColumnARows
.Cells(I, lColumn).Value = B
Next I
' adjust column counter
lColumn = lColumn + 1
End If
' next
lColumn = lColumn + 1
Loop
End With
' end
Beep
End Sub
[/pre]
-----


Just advise if any issue.


Regards!
 
@Luke M

Hi!

Do you say I didn't check the topic before posting again? Don't dare to do that...

Regards!
 
Hi Guys!


I am attaching my sample file.


https://dl.dropboxusercontent.com/u/35552808/SAMPLE-CHANDOO.xlsx


The number of columns will always vary.


This might make it easier to create the macro....THANKS!! :)
 
Hi, therese!


I didn't test Luke M's macro but mine works fine with your file. You only have to replace this line:

For I = 2 To lColumnARows

by this:

For I = 1 To lColumnARows

if you don't want a different title, otherwise adjust value of constant "ksTitle".


Don't forget to properly format original columns with headers starting with "a" so as to keep it in the inserted columns (004 vs. 4, 043.1 vs. 43.1).


Regards!
 
Hi, therese!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top