VB Questionmarks in a For loop
 

I've found a couple of posts similar to what i need, but i'm still having
issues with it. i'm trying to insert a formula into a cell via VB, and i'm
not doing the quotations correct i think. i just went ahead and put the
generic formula in there with no extra messy quotation marks just to
hopefully help this make more sense. any ideas?

For Row = 2 To 2 Step 1
Sheets(12).Select
Sheets(12).Copy Befo=Sheets(12)
Range("C7").Select

'***here's where i get the problem
ActiveCell.FormulaR1C1 = " =IF(MIN('Sheet1'!I"& Row &":S"& Row
&")<MAX('Sheet1'!I"& Row &":S"& Row &"),("$"&MIN('Sheet1'!I"& Row &":S"& Row
&") & " to $" & MAX('Sheet1'!I"& Row &":S"& Row &")),"$"&MAX('Sheet1'!I"& Row
&":S"& Row &")) " "


Next Row

thanks in advance

~jay

VB Questionmarks in a For loop
 

Couple of things,

1. don't use Row as a variable, it can only cause grief
2. I didn't really understand what all the $ and to in the formula was, so I
may have messes it up, but it does run <g

Dim sFormula As String
Dim nRow As Long

For nRow = 2 To 2 Step 1
Sheets(2).Copy Befo=Sheets(2)

sFormula = "=IF(MIN('Sheet1'!I" & nRow & _
":S" & nRow & ")<MAX('Sheet1'!I" & _
nRow & ":S" & nRow & ")," & _
"MIN('Sheet1'!I" & nRow & _
":S" & nRow & ")," & _
"MAX('Sheet1'!I" & nRow & ":S" & nRow & "))"
Range("C7").Formula = sFormula

Next nRow


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"J" wrote in message
...
I've found a couple of posts similar to what i need, but i'm still having
issues with it. i'm trying to insert a formula into a cell via VB, and i'm
not doing the quotations correct i think. i just went ahead and put the
generic formula in there with no extra messy quotation marks just to
hopefully help this make more sense. any ideas?

For Row = 2 To 2 Step 1
Sheets(12).Select
Sheets(12).Copy Befo=Sheets(12)
Range("C7").Select

'***here's where i get the problem
ActiveCell.FormulaR1C1 = " =IF(MIN('Sheet1'!I"& Row &":S"& Row
&")<MAX('Sheet1'!I"& Row &":S"& Row &"),("$"&MIN('Sheet1'!I"& Row &":S"&
Row
&") & " to $" & MAX('Sheet1'!I"& Row &":S"& Row &")),"$"&MAX('Sheet1'!I"&
Row
&":S"& Row &")) " "


Next Row

thanks in advance

~jay

VB Questionmarks in a For loop
 

thanks for the reply! so my problem lies in you comment about the $'s. i'm
trying to put together a string of cell values along with characters. a quick
example is having values in B1 and B2 and setting another cell equal to:

="beginning text "&B1& " Middle text " &B2& "end text"


that creates a string of text for the value of the cell, incorporating the
values of the cells B1 and B2. In my first example, the $'s were part of the
text. anyways, i dont know how to put the quotation marks into the cell with
a VB command like that.





"Bob Phillips" wrote:

Couple of things,

1. don't use Row as a variable, it can only cause grief
2. I didn't really understand what all the $ and to in the formula was, so I
may have messes it up, but it does run <g

Dim sFormula As String
Dim nRow As Long

For nRow = 2 To 2 Step 1
Sheets(2).Copy Befo=Sheets(2)

sFormula = "=IF(MIN('Sheet1'!I" & nRow & _
":S" & nRow & ")<MAX('Sheet1'!I" & _
nRow & ":S" & nRow & ")," & _
"MIN('Sheet1'!I" & nRow & _
":S" & nRow & ")," & _
"MAX('Sheet1'!I" & nRow & ":S" & nRow & "))"
Range("C7").Formula = sFormula

Next nRow


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"J" wrote in message
...
I've found a couple of posts similar to what i need, but i'm still having
issues with it. i'm trying to insert a formula into a cell via VB, and i'm
not doing the quotations correct i think. i just went ahead and put the
generic formula in there with no extra messy quotation marks just to
hopefully help this make more sense. any ideas?

For Row = 2 To 2 Step 1
Sheets(12).Select
Sheets(12).Copy Befo=Sheets(12)
Range("C7").Select

'***here's where i get the problem
ActiveCell.FormulaR1C1 = " =IF(MIN('Sheet1'!I"& Row &":S"& Row
&")<MAX('Sheet1'!I"& Row &":S"& Row &"),("$"&MIN('Sheet1'!I"& Row &":S"&
Row
&") & " to $" & MAX('Sheet1'!I"& Row &":S"& Row &")),"$"&MAX('Sheet1'!I"&
Row
&":S"& Row &")) " "


Next Row

thanks in advance

~jay