Help setting an R1C1 formula... 2

[webcats]

Hi,

I have a formula which works in my spreadsheet, but I need to set it with VBA after loading the data.

I've done easy formulas like: ActiveCell.Offset(0, 1).FormulaR1C1 = "=RC[1]-R[-1]C[1]"

which work fine, but this one is more complicated.

The formula as it works in the spreadsheet is:
'(Cell=D4)

=IF(ISNA(E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE)),0,E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE))

I've tried to code it into vba as:
'(ActiveCell=C4)

ActiveCell.Offset(0, 1).FormulaR1C1 = "=IF(ISNA(R[1]C[+1]-VLOOKUP(R1C[-2]&DATE(YEAR(R[1]C[-2]),MONTH(R[1]C[-2]),DAY(R[1]C[-2])-1),$A$4:$E$30328,5,FALSE)),0,R[1]C[+1]-VLOOKUP(R[1]C[-2]&DATE(YEAR(R[1]C[-2]),MONTH(R[1]C[-2]),DAY(R[1]C[-2])-1),$A$4:$E$30328,5,FALSE))"

But I keep getting an "Application-defined or object-defined error"

Can anyone tell me what I'm doing wrong?

Thanks!

 

[SkipVought]

Hi,

I detest R1C1 notation. It is very difficult to code and debug.

You have mixed notation in your formula.

What is your requirement for building the formula on the fly?



Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[webcats]

Hi Skip,

I also hate R1C1 notation...

The data that the formula uses gets written each time the program is run. I'm pulling data in from an Access database.

Basically what the formula does is subtracts the previous day's cumulative total from today's cumulative total to get the daily total.

Is there another way to do it besides using the R1C1 format?

Thanks.

 

[SkipVought]

[tt]
ActiveCell.Offset(0,1).Formula="=IF(ISNA(E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE)),0,E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE))"
[/tt]
or what am I missing???

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[webcats]

Sorry, I forgot to say that I have to loop through an unknown amount of cells and put the formula in each one.

I currently have about 1561 cells and the number will grow every day.

 

[SkipVought]

what about copy 'n' paste...

with ActiveCell
  .Offset(0,1).Formula="=IF(ISNA(E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE)),0,E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE))"
  .Offset(0,1).copy
  range(.offset(0,1), cells(.currentregion.rows.count, .offset(0,1).column)).paste
end with

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[webcats]

I don't think that will work as I have a different formula if the day is the 22nd:

Do While ActiveCell.Value <> ""
        If Day(ActiveCell.Value) = 22 Then
            ActiveCell.Offset(0, 1).FormulaR1C1 = "=RC[1]"
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.Offset(0, 1).FormulaR1C1 = "=IF(ISNA(R[1]C[+1]-VLOOKUP(R1C[-2]&DATE(YEAR(R[1]C[-2]),MONTH(R[1]C[-2]),DAY(R[1]C[-2])-1),$A$4:$E$30328,5,FALSE)),0,R[1]C[+1]-VLOOKUP(R[1]C[-2]&DATE(YEAR(R[1]C[-2]),MONTH(R[1]C[-2]),DAY(R[1]C[-2])-1),$A$4:$E$30328,5,FALSE))"
            ActiveCell.Offset(1, 0).Select
        End If
    Loop

Maybe it would just be easier to pull in two sets of data and leave the formula alone.

If I pull in the Date and the Cumulative Amount, then just run the code to change the formula if the day is the 22nd...

Hmmm, I'll have to look at that.

Thanks.

 

[SkipVought]

tilt

With ActiveCell
  .Offset(0, 1).Formula ="=IF(ISNA(E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE)),0,E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE))"

  .Offset(0, 1).Copy
  ws.Range(ws.Cells(1, .Offset(0, 1).Column), ws.Cells(.CurrentRegion.Rows.Count, .Offset(0, 1).Column)).PasteSpecial
End With

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[cheerio]

Going back to the original post - Tools, Options, General and a tick in R1C1 reference style will allow you to see the original formula in R1C1 style. At least that way you will not have a mixed formula.

In an ealier version of Excel there was a memory leak that caused Excel to crash if I called post/copy repeatefly from within VBA. This was with large amounts of data (copying whole sheets) but I tend now to be wary.

Changing the active cell is very slow and it is usually possible to code in such a way that this is not required.

 

[webcats]

Thanks cheerio, that helped me figure out how to enter the formula correctly. The problem was the mixed notation, as you and Skip pointed out.

Now to fix the extreemely slow code. Changing the active cell really is slow, like you said. I guess I didn't notice it before when I was only changing 20 or so cells.

 

[SkipVought]

How Can I Make My Code Run Faster? faq707-4105

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]