Help!! (vb macro in excel)

[Duncan]

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

 

[Duncan]

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]

First thing I'd try is to change this:
ActiveCell.Formula =
to
ActiveCell.FormulaR1C1 =

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

 

[Duncan]

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]

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!)