Add a formula in a cell that refers to another cell... Not using 'Range' tho 2

[MattGreer]

I've always had issues with 'Range', something about it has always frustrated and confused me, so for this particular bit of code I'm using "Cells" which makes complete and total sense to me, heh. Anyway...

In Excel 365 I'm running down a series of rows. I check in Column 8 and if there's a double quote → " then I want to add a formula to Column 9 that refers to Column 8. I'm not certain how to do this. The formula is a custom function to convert Feet and Inches to decimal feet.

'Somewhere at the top of the code I have this:
[indent]dq = Chr(34) 'double quote as a variable[/indent]
[indent]iLength = 9 'just so I can refer to the column easier[/indent]
[indent]iQty = 8 'just so I can refer to the column easier[/indent]

'Then later on...
        If InStr(1, Cells(iRow, iQty), dq) Then
            'Add the calc to column 9
            Cells(iRow, iLength).Formula = "=StringToFt(" &
        End If

So what goes after "=StringToFt(" ?



Thanks!!


Matt

 

[mintjulep]

something along the line of

Cells(iRow, iQty).Address

 

[MattGreer]

Did you hear that slapping sound? That was me smacking my forehead...

Thank you!

Thanks!!


Matt

 

[MattGreer]

I might have mis-typed it, I'm getting the error: "Run-time error '1004': Application-defined or object-defined error"

I'll update/edit this post if I figure out it's my error. This is the line of code:

Cells(iRow, iLength).Formula = "=StringToFt(" & Cells(iRow, iQty).Address

When I type the formula in manually, it works.

Any ideas?


edit: I forgot the closing paren. Do you hear that slapping sound again? My forehead is taking a beating today.


Thanks!!


Matt

 

[Andrzejek]

I can see Matt going
"convert Feet and Inches to decimal feet" - join at least XIX century and go metric.

Looks like you can use your [tt]StringToFt[/tt] function 'directly' (not as a formula):

Cells(iRow, iLength).[blue]Value[/blue] = [red]StringToFt[/red](Cells(iRow, iQty).[blue]Value[/blue])

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

Andrzejek,

That's not a bad idea really. I'm not sure if I should leave it in there or not. I think I like your idea better, but hey, I learned something so... Win Win!


Thanks!!


Matt

 

[Andrzejek]

I have always thought going metric is a very good idea.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

I have always thought going metric is a very good idea.

So did my son's sixth grade teacher in 1979, when she unilaterally decided NOT to teach English system conversions but only metric conversions (moving a decimal point) BECAUSE she wholeheartedly believed that the US was going to convert to metric in 1980.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

As an engineer, metric is amazing. That's all I have to say about that.

Thanks!!


Matt