Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Creating a Macro that will run functions in certain columns.

Hi Everyone!

I have 3 tabs "Instructions", "Raw_Data", and "Clean_Data". In the Clean_Data tab I have the following formulas written directly on the calc spreadsheet.

In Column A: =IF(ISNUMBER($Raw_Data.A1)=1,$Raw_Data.A1,"WRONG!")

In Column B: =IF(ISNUMBER($Raw_Data.B1)=1,$Raw_Data.B1,"WRONG!")

In Column C: =IF(ISNUMBER($Raw_Data.C1)=1,$Raw_Data.C1,"WRONG!")

In Column E: =$Raw_Data.B1

In Column F: =IF(ISNUMBER($Raw_Data.A1)=1,"I'm a number",IF(LEFT($Raw_Data.A1)="*",MID($Raw_Data.A1,2,4),MID($Raw_Data.A1,1,4)))

In Column G: =IF(F453="I'm a number"," ",IF(MID($Raw_Data.A1,6,4)="DUPL"," ",IF(MID($Raw_Data.A1,7,4)="DUPL"," ",IF(MID($Raw_Data.A1,6,1)="/",MID($Raw_Data.A1,7,4),MID($Raw_Data.A1,6,4)))))

In Column H: =IF(MID($Raw_Data.A1,6,4)="DUPL","DUPL",IF(MID($Raw_Data.A1,7,4)="DUPL","DUPL",IF(MID($Raw_Data.A1,11,4)="DUPL","DUPL",IF(MID($Raw_Data.A1,12,4)="DUPL","DUPL"," "))))

In Column I: =IF(LEFT($Raw_Data.A1)="",""," ")

Is there anyway I can enter these formulas into a macro and have them run through all the rows in the raw data sheet that contain text and the print the formula values on the clean data sheet? I'm trying to automate as much as possible and limit the chances for someone to be able to change the formulas by mistake.

Thanks for you help!