Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help!! (vb macro in excel)
Hello anyone-that-can-help-me.
I am fairly new to macros within excel and have tried various differant ways to get what i want to happen. I have some formulas which work perfectly but i want to create a tracking spreadsheet with 5 tabs consisting of (in the end) 65000 entries. If i copy the formulas down (with an isblank(true) so it only shows me the ones with stuff in) the file size is massive, so i tried to get a macro to put the formulas in the row when something is put in collumn A , i got this to work but it constantly refers to cell a2 for the answers (which is what the formula states) because the macro wont recognise to update the formula for the next line (which excel does by default), after it pastes the formula in it then goes on to convert it to a value so all i am left with is the answer (to save file size again) which is also working. Its just getting it to refer to the next cell and so on and so on or else it is a massive list of the same information! I will paste the code below so you can see where i am, i am really stumped now and dont know where to go next. please help me? Private Sub Worksheet_Change(ByVal Target As Excel.range) Application.EnableEvents = False ActiveCell.Offset(-1, 1).range("A1").Select ActiveCell.Formula = "=IF(A2=ISBLANK(TRUE),1,MID(A2,5,7))" ActiveCell.Offset(0, 1).range("A1").Select ActiveCell.Formula = "=IF(A2=ISBLANK(TRUE),1,VLOOKUP(VALUE(MID(A2,12,2) ),Sheet2!A2:B43,2))" ActiveCell.Offset(0, 1).range("A1").Select ActiveCell.Formula = "=IF(A2=ISBLANK(TRUE),1,MID(A2,1,1)+2000)" ActiveCell.Offset(0, 1).range("A1").Select ActiveCell.Formula = "=IF(A2=ISBLANK(TRUE),1,datestamp())" ActiveCell.Offset(1, 0).range("A1").Select ActiveCell.Offset(0, -4).range("A1").Select ' these next () paragraphs are converting the calculated formulas to values to save file size and ' also to stop the updation of the auto date ActiveCell.Offset(-1, 1).range("A1").Select Selection.Copy ActiveCell.Offset(0, 0).range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(0, 1).range("A1").Select Selection.Copy ActiveCell.Offset(0, 0).range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(0, 1).range("A1").Select Selection.Copy ActiveCell.Offset(0, 0).range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(0, 1).range("A1").Select Selection.Copy ActiveCell.Offset(0, 0).range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).range("A1").Select ActiveCell.Offset(0, -4).range("A1").Select Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help!! (vb macro in excel)
Me again, I have used the R1C1 function on reccomendation and that
solved much of the above problem. The only thing I am stumped on now is how to remove the ' ' from the line below. I left the cell reference for the vlookup as a2:b43 as it is on another sheet and i didnt put th $ in as i thought it didnt need it (and also it didnt like the $!) but it puts the formula in as 'A2':'B43' which returns a VALUE# ActiveCell.Offset(0, 1).range("A1").Select ActiveCell.Formula = "=IF(R[0]C[-2]=ISBLANK(TRUE),1,VLOOKUP(VALUE(MID(R[0]C[-2],12,2)),Sheet2!A2:B43,2))" How would I get the vlookup to remain as a static reference to sheet 2? Duncan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help!! (vb macro in excel)
First thing I'd try is to change this:
ActiveCell.Formula = to ActiveCell.FormulaR1C1 = Duncan wrote: Me again, I have used the R1C1 function on reccomendation and that solved much of the above problem. The only thing I am stumped on now is how to remove the ' ' from the line below. I left the cell reference for the vlookup as a2:b43 as it is on another sheet and i didnt put th $ in as i thought it didnt need it (and also it didnt like the $!) but it puts the formula in as 'A2':'B43' which returns a VALUE# ActiveCell.Offset(0, 1).range("A1").Select ActiveCell.Formula = "=IF(R[0]C[-2]=ISBLANK(TRUE),1,VLOOKUP(VALUE(MID(R[0]C[-2],12,2)),Sheet2!A2:B43,2))" How would I get the vlookup to remain as a static reference to sheet 2? Duncan -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help!! (vb macro in excel)
Oops. I didn't notice this the first time:
ActiveCell.FormulaR1C1 = _ "=IF(R[0]C[-2]=ISBLANK(TRUE),1,VLOOKUP(VALUE(MID(R[0]C[-2],12,2))," & _ "Sheet2!A2:B43,2))" is a mixture of both R1C1 and A1 reference style. ActiveCell.FormulaR1C1 = _ "=IF(R[0]C[-2]=ISBLANK(TRUE),1,VLOOKUP(VALUE(MID(R[0]C[-2],12,2))," & _ "Sheet2!r2c1:r43c2,2))" might work better. (I was blinded with all the rc stuff that was at the beginning of the formula!) Duncan wrote: I did try changing it to activecell.formular1c1 = but that didnt work either. I decided that I could manage without that line (the vlookup was only declaring what type of document it was) and i decided that sheet would be document specific which saved me the headache! anyway I am posting to say thank you for your help. Many thanks Duncan -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming |