Results 1 to 10 of 10
Thread: Logic

20090804, 15:27 #1
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Can anybody spot the problem with my logic, I'm getting #NAME.
RngFind.Offset(0, 3) = "=IF(RngFind.Offset(0, 7)>0,""Debit"",""Credit"")"
Thanks

20090804, 15:47 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
You're trying to set the formula of a cell. This formula should use worksheet functions, but RngFind.Offset(0, 7) is VBA, not a worksheet function. A cell formula doesn't have the slightest idea what RngFind is, and OFFSET works differently in formulas than in VBA.
You could use
RngFind.Offset(0, 3).FormulaR1C1 = "=IF(RC[10]>0,""Debit"",""Credit"")"
Homework: try to figure out why the formula uses RC[10].

20090804, 16:06 #3
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='787561' date='04Aug2009 20:47']Homework: try to figure out why the formula uses RC[10].[/quote]
10 columns to the left
Thanks, that works.
I'm now using:
Code:RngFind.Offset(0, 3).FormulaR1C1 = "=IF(RC[10]>0,""Debit"",""Credit"")" RngFind.Offset(0, 5).FormulaR1C1 = "=TEXT(RC[10],""dd/mm/yy"")" RngFind.Offset(0, 6).FormulaR1C1 = "=MID(RC[18],18,52)" RngFind.Offset(0, 8).FormulaR1C1 = "=MID(RC[19],71,4)&"" ""&MID(RC[19],77,3)" RngFind.Offset(0, 9).FormulaR1C1 = "=IF(RC[16]>0,RC[16],RC[15])"

20090804, 16:11 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
If you want to set the value, perform the If in VBA:
Code:If RngFind.Offset(0, 7) > 0 Then RngFind.Offset(0, 3) = "Debit" Else RngFind.Offset(0, 3) = "Credit" End If

20090804, 16:46 #5
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks.
I think I will use paste special values later on which will mean I can still see the formula's by commenting out the command, should I need to.
RngFind.Offset(0, 9).FormulaR1C1 = "=IF(RC[16]>0,RC[16],RC[15])"
What is the correct syntax for adding ".NumberFormat = "#,##0.00"" here?

20090804, 16:55 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Code:With RngFind.Offset(0, 9) .FormulaR1C1 = "=IF(RC[16]>0,RC[16],RC[15])" .NumberFormat = "#,##0.00" End With

20090804, 17:41 #7
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts

20090805, 09:09 #8
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
RngFind.Offset(0, 6).FormulaR1C1 = "=MID(RC[18],18,52)"
Would it be possible to remove trailing spaces here? I have to take 52 as this is the maximum, but I would like to ignore any space characters at the end.
TIA.

20090805, 10:12 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
RngFind.Offset(0, 6).FormulaR1C1 = "=TRIM(MID(RC[18],18,52))"

20090805, 10:30 #10
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks
RngFind.Offset(0, 6).FormulaR1C1 = "=TRIM(MID(RC[18],18,52)" ")" ...... is what I was trying