Macro to act differently on selected Multiple Columns

[e4excel]

Dear Forum,

I have data spread over several columns and this data is added from different files which has a different format than the one required to be in the Consolidated File.

I get results from a portal for Insurance certification, which has Exam Date, Issue Date and Validity Date which is copied manually and pasted from this file and to my master consolidated sheet...then I have to fetch other details for these certified employees from the HR Database which has the data in different formats...

So have to manually copy the format from the previous rows and then copy to the copied data, since this is done more than once in a day its tedious and time consuming so can this be done with a Macro..

I made my own small code but dont know how to make it act differently on selected columns....

I would select the entire matrix and then want the macro to run differently, taking into consideration the column names

Sub Text_Selection()
Dim myRange As Range
Set myRange = Selection

'This is for COl B
  
Selection.TextToColumns Destination:=myRange, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
End Sub

I also need to add a vlookup formula for some column based on the column B's value...

Ex: ;this is for Col C

=if(isna(vlookup($B2,Emp Dump$A:$AA,17,0)),if(isna(vlookup($B2,Emp Dump$A:$AA,17,0)),vlookup($B2,Emp Dump$A:$AA,17,0), vlookup($B2,Emp Dump$A:$AA,17,0)))

So how do I write the code in such a way that the code acts on its own differently

Regards
e4excel

[e4excel]

I am adding an attachment for reference..

The Green Areas are actually form 2 separate files but for confidential reasons the same cannot be added so I am adding any two records for explanation..

Every column would have a different column no to in the area marked in yellow..and I would be selected this Yellow portion and then running the macro which needs to have the same formatting as is in the first 2 records..

Regards
e4excel

[e4excel]

Dear Forum,

Can someone help me with this I just need to select more than one Column Range and the Macro needs to act differently on each column separately..

Let's say Col A and Col B would have different Formatting as well as there would be a formula to be inserted in the column B and some formatting...

I can create separate Suns for each Column but how do I trigger them in the common selection?

Regards
e4excel

[xlbiznes]

@e4excel ,

the file that you have posted is not very informative, please replace the data with non relevant data and make it more clear for the forum members to understand your needs.

The green area that you are referring to has emp_no and name, so what needs to be done
Seriously you need to make another proper file to make things more clearer.

[e4excel]

Dear xlbzines...

What I intend to do?

I will be entering new employee nos in the sheet SP List in Column B as plain General Nos.. This is the list of certified employees whose information is fetched from 2 separate files Emp_Retail and Emp_Premier...

IF(ISNA(VLOOKUP($B4,$A$13:$C$17,2,0)),VLOOKUP($B4,$A$20:$C$23,2,0),VLOOKUP($B4,$A$13:$C$17,2,0))

Now after I run the formula I would be selecting the entire data from lets say...B4:S7 coloured in yellow in which I need to have the exact same formatting as the one in the above two rows...

I know how to do it for a single column, but do now know how that can be done on Multiple Column Selection..so that the Formatting Macro makes the contents in the col B Emp Nos as text and then the remaining columns get their Formatting differently..

Regards
e4excel

[xlbiznes]

so by default can we take the formatting in the row 2 i.e : (B2:S7) ? and apply this to the selected range.

[e4excel]

Yes, that's what I want but would appreciate if you could also show me also how to do the formatting bit on more than one column based on the selection using the approach mentioned in my first post...

The reason I am saying is in case if the formatting is missing in the previous columns, then the same would get copied by default, however if I have it vba coded then this would always remain the same

[e4excel]

Yes, that's what I want but would appreciate if you could also show me also how to do the formatting bit on more than one column based on the selection using the approach mentioned in my first post...

The reason I am saying is in case if the formatting is missing in the previous columns, then the same would get copied by default, however if I have it vba coded then this would always remain the same

[xlbiznes]

the thumb rule for this code to work properly is that , we are assuming that row 2 of your data has the needed formatting set.

try this code :

Sub format_range()
Dim rng As Range
Set rng = Selection
Application.ScreenUpdating = False
For x = 1 To rng.Columns.Count
Cells(2, rng.Columns(x).Column).Copy
rng.Columns(x).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Next
Application.ScreenUpdating = True
End Sub

[xlbiznes]

can you relate to the code that i have posted and then we take it further.

[e4excel]

Thanks a lot, xlbiznes...

But there is a small issue, the first column B in the selection should be marked as text and therefore i do it the Text to Column way when i tried to copy the formatting for some reason it did not make it into text..

The reason is that this employee code is text in all other files and therefore i have to explicitly change the format to text...

Can you please show me how to write the code only for 2 columns and I will manage the rest..

Thanks in advance...

I would prefer to have the formatting for each column written seprately and then i should be able to apply the same dynamically..

Regards
e4excel

[e4excel]

Thanks for all the efforts and sorry for posting a late response...I was not able to post a reply as my net was veryl slow...

This is very close and also beneficial but my idea was to get the First Row no as the one in selection which could be from an already existing filled column with lets say 11 cells from cell A1 till A11 and if I select the cells or a range of columns starting From A5 till C8 then I want the First Row to be 5 and the Last Row to be 8 though technically the first filled row no = 1 and the last row no = 11.


Is this possible in this style of coding..

Sub Col_1()


Dim StartRow As Integer
Dim LastRow As Integer
Dim wsSP_List As Worksheet

Set wsSP_List = Worksheets("SP List")

With wsSP_List

StartRow = ? * /Selected First Row No 
LastRow = ?  * /Selected Last Row No 

With .Range(.Cells(StartRow, 1), .Cells(LastRow, 1))    'Column Heading - Cluster Name
         
 .Formula = "=IF(ISNA(VLOOKUP($AJ2,Dump!$AJ:$AN,5,0)),$AK2,VLOOKUP($AJ2,Dump!$AJ:$AN,5,0))"
 .Value = .Value
 .Alignment = XlLeft


End With

End with

End Sub

Thanks & regards
e4excel

[xlbiznes]

try something like this :

'let us say you have 3 columns make an array first
Dim my_formats(1 To 3) As String
my_formats(1) = "@" 'Number column 1
my_formats(2) = "dd-mmm-yy" 'Date column 2
my_formats(3) = "0" 'Numeric column 3
'you can apply the formats based on the column of the selection
For x = 1 To Selection.Columns.Count
Selection.Columns(x).Cells.NumberFormat = my_formats(Selection.Columns(x - 1).Column)
Next

[e4excel]

Hey thanks xlbzines...

But I actually intended to change a lot of things for each column along with the format, Text Alignment and also was contemplating putting formulas and so each for each column would be atleast 6-7 line of code...

I would have to get the different values based on vlookup with changing column no form the main file..so i wanted to actually get a formula and also change the alignment..

Regards
e4excel

[e4excel]

Thanks Nevertheless but what I was thinking all along was that based on my selection some code which could be able to seperately call different subs based on the columns..

[xlbiznes]

the other way around is to have a template sheet with the needed formatting and formula's done for each specific column
for one 1 single row of dummy data.

Use this template to apply the needed (formula & formats) to the selection.

[e4excel]

Thanks for the alternative solution, but is there a way of just identifying the columns in the selection..

Like I selected just three columns and then i have just three different subs for each column A, B and C..
let's say - COl A - Left Alignment , COl B - Center ALignment and the COl C - Right ALignment..

SO after selecting the area in three columns from say A100 till C110 it can act differently for each column...

Regards
e4excel

[e4excel]

Thanks a lot xlbzines for all the help and support, but i am still held up at one point...

I had kept this project on hold as I was awaiting your help..

Now there's one small issue is that in the example i just showed a very simple formula of ="a2*1"

However, in reality I need to use 2 different vlookups from different from 2 different Files if one vlookup fails and more importantly the VLOOKUP Lookup value is actually the employee id entered in the column B...

SO it cannot be B2 but should be any cell which is selected so the row no should be actually Bn where "n" will be first row in selection and then the subsequent rows...

Now, my actual formula will be as mentioned below:

=IF(ISNA(VLOOKUP($B4,'SP List'!$A$12:$M$14,2,0)),VLOOKUP($B4,'SP List'!$A$19:$M$21,2,0),VLOOKUP($B4,'SP List'!$A$12:$M$14,2,0))

The column no 2 is for getting the value in the column C and the Column D will have the same formula but with 3 as the column no..

Now the ranges shown as "'SP List'!$A$12:$M$14" and "'SP List'!$A$19:$M$21" are for explanation purpose..

Actually I would be searching in 2 different files with the same structure so I can simply keep that absolute....like "'Cust_Details_1!$A:$AZ" OR "'Cust_Details_2!$A:$AZ"..

But I dont know how to write the same in vlookup where the row is dynamic..

Thanks a million...

Regards
e4excel

[xlbiznes]

try something like this :

Sub apply_formatting()
Dim rng As Range
Dim my_style(1 To 3) As Variant 'create an array to store your formatting
my_style(1) = xlLeft
my_style(2) = xlCenter
my_style(3) = xlRight

Set rng = Selection
For Each col In rng.Columns 'loop through the columns selected
    If col.Column <= 3 Then
        ActiveSheet.Columns(col.Column).HorizontalAlignment = my_style(col.Column)
    End If
Next
End Sub

[e4excel]

Thanks a lot xlbzines...

This is exactly what i was looking out for however I have to add lot of things in each Column Sub so how do I do that with the my_style(1) ?

I mean I would prefer to either name the Macro with the Col_1 and then formatting features and some formulas added to it..

So what syntax would need to be added ?

Regards
e4excel

[e4excel]

Dear xlbzines...

This is really very helpful however I want to add formulas to different Columns with the same VLOOKUP with the same Lookup value but with different Column nos and the formatting will also be different for different columns based on the content like the dates in certain columns will have date formats like "dd-mmm-yy" for values entered as "mm/dd/yyyy" coming out of vlookup formula which is again from different files...

So can you assist on one small sample, how do i incorporate the same using your code for the entire selection..

Like i want the vloolkup to be used in the selected area as well as the formatting as thats going to be same all the time..so that i can simply enter the Emp IDs and then select and run the macro for the selected portion or the entire data as the data needs to be updated when the employees resign or when they changes their Business Locations..

So can you guide me on that part too...

Regards
e4excel

[xlbiznes]

If you need to call a macro then you might need to write a case statement to use the col.column value to call the appropriate macro. Like this

For Each col In rng.Columns 'loop through the columns selected
select case col.column
case 1
macro1
case 2
macro3
end select
next

[e4excel]

If you need to call a macro then you might need to write a case statement to use the col.column value to call the appropriate macro. Like this

For Each col In rng.Columns 'loop through the columns selected
select case col.column
case 1
macro1
case 2
macro3
end select
next

Dear xlbzines...
Thanks gain for the modified code..I am presenting my code please also guide me as to how I can manouvre the Start Row and the Last Row which is actually the selected row and the last row in selection..

Sub Col_1()


Dim StartRow As Integer
Dim LastRow As Integer
Dim wsSP_List As Worksheet

Set wsSP_List = Worksheets(SP List")

With wsSP_List

StartRow = ?
LastRow = ?

With .Range(.Cells(StartRow, ??), .Cells(LastRow, ??))    'Column Heading - Cluster Name
         
 .Formula = "=IF(ISNA(VLOOKUP($AJ2,Dump!$AJ:$AN,5,0)),$AK2,VLOOKUP($AJ2,Dump!$AJ:$AN,5,0))"
 .Value = .Value
 .Alignment = XlLeft


End With

End with

End Sub

[e4excel]

If you need to call a macro then you might need to write a case statement to use the col.column value to call the appropriate macro. Like this

For Each col In rng.Columns 'loop through the columns selected
select case col.column
case 1
macro1
case 2
macro3
end select
next

Dear xlbzines...
Thanks gain for the modified code..I am presenting my code please also guide me as to how I can manouvre the Start Row and the Last Row which is actually the selected row and the last row in selection..

Sub Col_1()


Dim StartRow As Integer
Dim LastRow As Integer
Dim wsSP_List As Worksheet

Set wsSP_List = Worksheets(SP List")

With wsSP_List

StartRow = ?
LastRow = ?

With .Range(.Cells(StartRow, ??), .Cells(LastRow, ??))    'Column Heading - Name
         
 .Formula = "=IF(ISNA(VLOOKUP($AJ2,Dump!$AJ:$AN,5,0)),$AK2,VLOOKUP($AJ2,Dump!$AJ:$AN,5,0))"
 .Value = .Value
 .Alignment = XlLeft


End With

End with

End Sub

[xlbiznes]

is this columns or rows.
Are you asking for the last row in a selection ?

[e4excel]

is this columns or rows.
Are you asking for the last row in a selection ?

The Rows both the StartRow and LastROw in selection..

Regards
e4excel

[xlbiznes]

check this file, i hope this is what you need.

[xlbiznes]

try this,

With Selection
startrow = .Rows(1).Row
lastrow = .Rows(.Rows.Count).Row
End With

MsgBox "First Row :" & startrow & vbCrLf & "Last Row : " & lastrow, vbInformation

[e4excel]

Thanks a lot again xlbzines for this code..I will check it and will post back...Gud nite...

[e4excel]

Dear xlbzines,

I am not able to manage the code, I need to write seperate subs for each column but dont know how to put in the FOr Loop alongwith the col.columns

and the code is throwing an error at the for each col statement...please advise..

Sub Selected_Format()

Dim wsTrail As Worksheet

Set wsTrail = Worksheets("Trail")

With wsTrail

With Selection
StartRow = .Rows(1).Row
LastRow = .Rows(.Rows.Count).Row
End With

MsgBox "First Row :" & StartRow & vbCrLf & "Last Row : " & LastRow, vbInformation


For Each col In rng.Columns 'loop through the columns selected
Select Case col.Column
Case 1

With .Range(.Cells(StartRow, 2), .Cells(LastRow, 2))    'Column Heading - Name
         
 .Formula = "=a2*1"
 .Value = .Value
 .Alignment = xlLeft


End With



Case 2

With .Range(.Cells(StartRow, 2), .Cells(LastRow, 2))    'Column Heading - Name
         
 .Formula = "=a2*1"
 .Value = .Value
 .Alignment = xlRight


End With

End Select
Next

End With

End Sub

Regards
e4excel

[xlbiznes]

Hi,

looking at your code you have not defined the variable rng as range and set the range to the selection.

so your code would look like this :

Sub Selected_Format()
Dim wsTrail As Worksheet
Set wsTrail = Worksheets("Trail")

Dim rng As Range
With wsTrail

With Selection
StartRow = .Rows(1).Row
'if you are going to select the columns then lastrow will always be 1048576
'so do something like this
'Rows(StartRow).End(xlDown).Row

LastRow = .Rows(.Rows.Count).Row
End With

MsgBox "First Row :" & StartRow & vbCrLf & "Last Row : " & LastRow, vbInformation

Set rng = Selection

For Each col In rng.Columns 'loop through the columns selected
Select Case col.Column
Case 1

With .Range(.Cells(StartRow, 2), .Cells(LastRow, 2))    'Column Heading - Name
         
 .Formula = "=a2*1"
 .Value = .Value
 .Alignment = xlLeft


End With



Case 2

With .Range(.Cells(StartRow, 2), .Cells(LastRow, 2))    'Column Heading - Name
         
 .Formula = "=a2*1"
 .Value = .Value
 .Alignment = xlRight


End With

End Select
Next

End With

End Sub