Please help me to fix my macro

okok

New Member
Joined
Mar 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi.

I'm new to doing macros in Excel, but after a lot of struggle I've managed to at least make a start.

So, I will be having a sheet of data, where each column represents the results of one experiment.
The data will occupy rows 2 to 72 in all columns.

For each experiment/column, I will need to process those data so I can then better represent them on my graphs.

So the macro I managed to make does the job for a single column. First, it adds next to it a couple of extra columns where the processed data will go. Then, the formulas work out the data. This was done for the first column (which is always B) and it does the job, but I will need to do it also for each of the other columns.

My macro is this (I managed to simplify the macro as much as possible):

VBA Code:
Sub Normalise_RD2()
'
' Normalise_RD2 Macro
'

'
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A73").FormulaR1C1 = "Max"
    Range("A74").FormulaR1C1 = "Min"
    Range("B74").Value = "=MIN(B2:B72)"
    Range("C2").FormulaR1C1 = "=RC[-1]-R74C[-1]"
    Range("C2").AutoFill Destination:=Range("C2:C72"), Type:=xlFillDefault
    Range("C73").Value = "=MAX(B2:B72)"
    Range("D2").FormulaR1C1 = "=RC[-1]/R73C[-1]"
    Range("D2").AutoFill Destination:=Range("D2:D72"), Type:=xlFillDefault
    Columns("D:D").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With
End Sub


My first question is whether there are any ways to simplify it even better.

My second and most important question is: This is only for column B, so how do I make the macro so that it contunues doing the same procedure for each of the rest of the columns?

Hope you get me, but ask me if you didn't.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

okok

New Member
Joined
Mar 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
In case this will help you understand what I need, I also edited the macro to be able to proceed to the next column of my experimental data (and also corrected a couple of mistakes I had on the macro I posted above). I will show you the macro how it is now. While it works fine to process the data of my first two columns, I want to automate it to be able to proceed to all the next columns so that I don't need to do this code again and again a thousand times.

VBA Code:
' Normalise_RD2 Macro
'

'
    Range("A73").FormulaR1C1 = "Max"
    Range("A74").FormulaR1C1 = "Min"
    
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B74").Value = "=MIN(B2:B72)"
    Range("C2").FormulaR1C1 = "=RC[-1]-R74C[-1]"
    Range("C2").AutoFill Destination:=Range("C2:C72"), Type:=xlFillDefault
    Range("C73").Value = "=MAX(C2:C72)"
    Range("D2").FormulaR1C1 = "=RC[-1]/R73C[-1]"
    Range("D2").AutoFill Destination:=Range("D2:D72"), Type:=xlFillDefault
    Columns("D:D").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With
    
    Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E74").Value = "=MIN(E2:E72)"
    Range("F2").FormulaR1C1 = "=RC[-1]-R74C[-1]"
    Range("F2").AutoFill Destination:=Range("F2:F72"), Type:=xlFillDefault
    Range("F73").Value = "=MAX(F2:F72)"
    Range("G2").FormulaR1C1 = "=RC[-1]/R73C[-1]"
    Range("G2").AutoFill Destination:=Range("G2:G72"), Type:=xlFillDefault
    Columns("G:G").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With
End Sub
 

okok

New Member
Joined
Mar 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I thought of an easier way to ask that might still help me achieve what I need.

So let's say for example that I have the following code in my macro, to make it add a couple of empty columns next to column F:

VBA Code:
 Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Now say that I also want to do the same thing to the rest of my columns, for example to add a couple of empty columns next to column H.

To save me from needing to past the above code and manually change the column letter over and over, is there a code that automatically says "do this thing for every second column"?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,304
Messages
5,641,430
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top