trying to get a macro to work

[cmefly]

hi all,

i've developed a macro but it seems not to be working.
It basically looks at cell C5 and based on it's value runs a certain part of the macro. (i used IFs and ELSEIFs in my macro). The thing is, it doesn't work...can someone tell me where i'm going wrong....??? and if i want to put this in the worksheet module so that it constantly works, how do i go about modifying the macro....?


here it is....

Sub Macro1()
If c5 = "July 1 /03 - Sept 30 /03" Then
b13 = _
"=VLOOKUP(RC[-1]&""close"", 'Q3_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q3_03 DATA'!R2C1:R2C136,0),FALSE)" ElseIf c5 = "Jan 1 /03 - March 31 /03" Then
b13 = _
"=VLOOKUP(RC[-1]&""close"", 'Q1_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q1_03 DATA'!R2C1:R2C136,0),FALSE)"
ElseIf c5 = "April 1 /03 - June 30 /03" Then
b13 = _
"=VLOOKUP(RC[-1]&""close"", 'Q2_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q2_03 DATA'!R2C1:R2C136,0),FALSE)"
ElseIf c5 = "Oct 1 /03 - Dec 31 /03" Then
b13 = _
"=VLOOKUP(RC[-1]&""close"", 'Q4_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q4_03 DATA'!R2C1:R2C136,0),FALSE)"
ElseIf c5 = "Oct 1 /02 - Dec 31 /02" Then
b13 = _
"=VLOOKUP(RC[-1]&""close"", 'Q4_02 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q4_02 DATA'!R2C1:R2C136,0),FALSE)"
End If
End Sub


thank you very much for you time....
any help would be greatly appreciated

cmefly

 

[just_jon]

Well, for one I think the B13 refs need to be [B13] -- B13 is a variable as stated, not a range object. Also, I believe it's good practice to reference formulas as [B13].Formula =

EDIT -- not sure what "...if i want to put this in the worksheet module so that it constantly works..." means

 

[Tana Lee]

Aloha...

Of course, the same for c5.

Tana-Lee

 

[just_jon]

Aloha...

Of course, the same for c5.

Tana-Lee

yup, missed that...

 

[Greg Truby]

I think you'll need to reference cell [B13] 's R1C1 formula property, not formula, i.e. [B13].FormulaR1C1

 

[Caliche]

Try:

b13.formulaR1C1= (your formula)

HTH

 

[cmefly]

thanks, i've made the changes....
however, the macro is still not working...

i'm changing the value in c5 but the formula in b13 isn't changing....
any suggestions???

 

[just_jon]

is is now [c5] ? are you running this from the worksheet module?

 

[cmefly]

hi jon,

here is the actual macro with all the little bits and bites....lol
every "if" section is identical except for the vlookup which grabs data from different worksheets.....
k, scrap the C5, it's now B3.....and yes, it's a worksheet module...**cough** or i'd like it to be....(i've already placed it in the worksheet module)

Sub Macro1()
If [B3].FormulaR1C1 = "July 1 /03 - Sept 30 /03" Then
[B13].FormulaR1C1 = _
"=VLOOKUP(RC[-1]&""close"", 'Q3_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q3_03 DATA'!R2C1:R2C136,0),FALSE)"
Range("B14").Select
Range("B13").Select
Selection.AutoFill Destination:=Range("B13:C13"), Type:=xlFillDefault
Range("B13:C13").Select
Range("C13").Select
[B13].FormulaR1C1 = _
"=VLOOKUP(RC[-2]&""open"", 'Q3_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q3_03 DATA'!R2C1:R2C136,0),FALSE)"
Range("B13:C13").Select
Selection.AutoFill Destination:=Range("B13:C21"), Type:=xlFillDefault
Range("B13:C21").Select
Range("B21:C21").Select
Selection.Copy
Range("B25:C25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B25:C31"), Type:=xlFillDefault
Range("B25:C31").Select
Range("B13").Select
ElseIf [B3].FormulaR1C1 = "Jan 1 /03 - March 31 /03" Then
[B13].FormulaR1C1 = _
"=VLOOKUP(RC[-1]&""close"", 'Q1_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q1_03 DATA'!R2C1:R2C136,0),FALSE)"
Range("B14").Select
Range("B13").Select
Selection.AutoFill Destination:=Range("B13:C13"), Type:=xlFillDefault
Range("B13:C13").Select
Range("C13").Select
[B13].Formula = _
"=VLOOKUP(RC[-2]&""open"", 'Q1_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q1_03 DATA'!R2C1:R2C136,0),FALSE)"
Range("B13:C13").Select
Selection.AutoFill Destination:=Range("B13:C21"), Type:=xlFillDefault
Range("B13:C21").Select
Range("B21:C21").Select
Selection.Copy
Range("B25:C25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B25:C31"), Type:=xlFillDefault
Range("B25:C31").Select
Range("B13").Select
ElseIf [B3].FormulaR1C1 = "April 1 /03 - June 30 /03" Then
[B13].FormulaR1C1 = _
"=VLOOKUP(RC[-1]&""close"", 'Q2_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q2_03 DATA'!R2C1:R2C136,0),FALSE)"
Range("B14").Select
Range("B13").Select
Selection.AutoFill Destination:=Range("B13:C13"), Type:=xlFillDefault
Range("B13:C13").Select
Range("C13").Select
[B13].FormulaR1C1 = _
"=VLOOKUP(RC[-2]&""open"", 'Q2_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q2_03 DATA'!R2C1:R2C136,0),FALSE)"
Range("B13:C13").Select
Selection.AutoFill Destination:=Range("B13:C21"), Type:=xlFillDefault
Range("B13:C21").Select
Range("B21:C21").Select
Selection.Copy
Range("B25:C25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B25:C31"), Type:=xlFillDefault
Range("B25:C31").Select
Range("B13").Select
ElseIf [B3].FormulaR1C1 = "Oct 1 /03 - Dec 31 /03" Then
[B13].FormulaR1C1 = _
"=VLOOKUP(RC[-1]&""close"", 'Q4_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q4_03 DATA'!R2C1:R2C136,0),FALSE)"
Range("B14").Select
Range("B13").Select
Selection.AutoFill Destination:=Range("B13:C13"), Type:=xlFillDefault
Range("B13:C13").Select
Range("C13").Select
[B13].Formula = _
"=VLOOKUP(RC[-2]&""open"", 'Q4_03 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q4_03 DATA'!R2C1:R2C136,0),FALSE)"
Range("B13:C13").Select
Selection.AutoFill Destination:=Range("B13:C21"), Type:=xlFillDefault
Range("B13:C21").Select
Range("B21:C21").Select
Selection.Copy
Range("B25:C25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B25:C31"), Type:=xlFillDefault
Range("B25:C31").Select
Range("B13").Select
ElseIf [B3].FormulaR1C1 = "Oct 1 /02 - Dec 31 /02" Then
[B13].FormulaR1C1 = _
"=VLOOKUP(RC[-1]&""close"", 'Q4_02 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q4_02 DATA'!R2C1:R2C136,0),FALSE)"
Range("B14").Select
Range("B13").Select
Selection.AutoFill Destination:=Range("B13:C13"), Type:=xlFillDefault
Range("B13:C13").Select
Range("C13").Select
[B13].FormulaR1C1 = _
"=VLOOKUP(RC[-2]&""open"", 'Q4_02 DATA'!R1C1:R40C136, MATCH(R5C2, 'Q4_02 DATA'!R2C1:R2C136,0),FALSE)"
Range("B13:C13").Select
Selection.AutoFill Destination:=Range("B13:C21"), Type:=xlFillDefault
Range("B13:C21").Select
Range("B21:C21").Select
Selection.Copy
Range("B25:C25").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B25:C31"), Type:=xlFillDefault
Range("B25:C31").Select
Range("B13").Select
End If
End Sub

 

[cmefly]

I should mention that B3 is a pulldown list