Prev: VBA command to move cursor
Next: Track Changes Colour
From: Rick on 28 Mar 2010 14:39
I need help....
The following statement is being used in a do loop.

ActiveCell.FormulaR1C1 = "=IF(" & TagNmeMe & "!R[" & Cnt1 &
"]C[-1]="""",""""," & TagNmeMe & "!R[" & Cnt1 & "]C[-1])"

What I need it to do is this:
if(sheet1!A5="","",sheet1!a5) then 9 rows down paste this:
if(sheet2!A5="","",sheet2!a5)

Problem is when recording a macro the cnt1 changes from nothing to 9 then to
18 but the formula does not mimic what recording did.
From: FSt1 on 28 Mar 2010 15:10
hi
try....
ActiveCell.Formula = "=IF(Sheet1!A5="""","""",Sheet1!A5)"
Activecell.offset(9,0).formula = "=IF(Sheet2!A5="""","""",Sheet2!A5)"

regards
FSt1

"Rick" wrote:

> I need help....
> The following statement is being used in a do loop.
>
> ActiveCell.FormulaR1C1 = "=IF(" & TagNmeMe & "!R[" & Cnt1 &
> "]C[-1]="""",""""," & TagNmeMe & "!R[" & Cnt1 & "]C[-1])"
>
> What I need it to do is this:
> if(sheet1!A5="","",sheet1!a5) then 9 rows down paste this:
> if(sheet2!A5="","",sheet2!a5)
>
> Problem is when recording a macro the cnt1 changes from nothing to 9 then to
> 18 but the formula does not mimic what recording did.
From: Rick on 28 Mar 2010 17:17
Thanks for the tip...
Modified your suggestion to this and it works perfectly.

ActiveCell.Offset(Cnt1, 0).Formula = "=IF(" & TagNmeMe & "!A5="""",""""," &
TagNmeMe & "!A5)"

"FSt1" wrote:

> hi
> try....
> ActiveCell.Formula = "=IF(Sheet1!A5="""","""",Sheet1!A5)"
> Activecell.offset(9,0).formula = "=IF(Sheet2!A5="""","""",Sheet2!A5)"
>
> regards
> FSt1
>
> "Rick" wrote:
>
> > I need help....
> > The following statement is being used in a do loop.
> >
> > ActiveCell.FormulaR1C1 = "=IF(" & TagNmeMe & "!R[" & Cnt1 &
> > "]C[-1]="""",""""," & TagNmeMe & "!R[" & Cnt1 & "]C[-1])"
> >
> > What I need it to do is this:
> > if(sheet1!A5="","",sheet1!a5) then 9 rows down paste this:
> > if(sheet2!A5="","",sheet2!a5)
> >
> > Problem is when recording a macro the cnt1 changes from nothing to 9 then to
> > 18 but the formula does not mimic what recording did.
From: FSt1 on 28 Mar 2010 17:36
great
thanks for the feed back.

regards
FSt1

"Rick" wrote:

> Thanks for the tip...
> Modified your suggestion to this and it works perfectly.
>
> ActiveCell.Offset(Cnt1, 0).Formula = "=IF(" & TagNmeMe & "!A5="""",""""," &
> TagNmeMe & "!A5)"
>
> "FSt1" wrote:
>
> > hi
> > try....
> > ActiveCell.Formula = "=IF(Sheet1!A5="""","""",Sheet1!A5)"
> > Activecell.offset(9,0).formula = "=IF(Sheet2!A5="""","""",Sheet2!A5)"
> >
> > regards
> > FSt1
> >
> > "Rick" wrote:
> >
> > > I need help....
> > > The following statement is being used in a do loop.
> > >
> > > ActiveCell.FormulaR1C1 = "=IF(" & TagNmeMe & "!R[" & Cnt1 &
> > > "]C[-1]="""",""""," & TagNmeMe & "!R[" & Cnt1 & "]C[-1])"
> > >
> > > What I need it to do is this:
> > > if(sheet1!A5="","",sheet1!a5) then 9 rows down paste this:
> > > if(sheet2!A5="","",sheet2!a5)
> > >
> > > Problem is when recording a macro the cnt1 changes from nothing to 9 then to
> > > 18 but the formula does not mimic what recording did.
 | 
Pages: 1
Prev: VBA command to move cursor
Next: Track Changes Colour