ActiveCell.FormulaR1C1

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.
0
Utf
3/28/2010 6:39:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
2020 Views

Similar Articles

[PageSpeed] 50

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.
0
Utf
3/28/2010 7:10:02 PM
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.
0
Utf
3/28/2010 9:17:01 PM
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.
0
Utf
3/28/2010 9:36:01 PM
Reply:

Similar Artilces:

ActiveCell.Text
I need a macro to delete any row in a spreadsheet that contains a certain word. I know how to do this for a number or formula, but how do I find a cell that contains a specific word? Could you not just just Find-Replace on the whole sheet (Ctrl A) and use an empty string "" as the replace string? Gromit ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Just use the word as the search string (the "FindWhat:=" argument). It should work just fine....

Re : Excel AutoFilter Execution and the ActiveCell thereof
An Excel worksheet contains an AutoFiltered Range (which in turn is composed of several Filters). When the worksheet user executes a Filter, how is it possible to follow suit that (via VBA coding in Worksheet_Calculate(), perhaps ?!) the Cell where the Down-Arrow resides (Trade-Mark of AutoFilter) shall become an ActiveCell ? Please share your experience. Regards. ...

use of integer in .FormulaR1C1
hello, I used integer A and B in formula such as ..FormulaR1C1 = "=R[" & -A & "]C*R[" & -B & "]C" and it works fine How can I use the same principle for the following: ..FormulaR1C1 = "=INDEX(RangeX,[" & A &"],MATCH(YearEnd,Years,0))" [" & A &"] does not seem to be recognised PS: it is important that I use .FormulaR1C1 as my user needs to see the formula used. Thanks a lot. -- caroline you are combining a string and a number which is giving you the issue try .FormulaR1C1 = "...

Formula to update cell w/ActiveCell.Value?
Can I put a formula in a certain cell that will, on recalculation, update that cell with the value of the current ActiveCell? Ed Ed, Don't think so. But this sub will do it. Paste it into the sheet module. Change the location as necessary. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False ' prevent retriggering Range("A1") = ActiveCell.Text Application.EnableEvents = True End Sub -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Ed" <Ed_Millis@NOSPAM.Ho...

ActiveCell
I am trying to achieve If ActiveCell In Range ("C22:K22,C24:K24") Then but I require assistance with the correct syntax. Thanks Sandy Try something like this: IF Not Intersect (ActiveCell, Range("C22:K22,C24:K24")) is Nothing THEN (Not really intuitive, is it?) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Sandy" <sandy_stephen@DELETEhotmail.com> wrote in message news:5B6BB6C6-B6CB-46A8-B998-C8BA24EA9FFB@microsoft.com... >I am trying to achieve > > If ActiveCell In Range ("C22...

reference to other worksheets in FormulaR1C1
Hi All, I want to create a formula: ActiveCell.FormulaR1C1 = "=SUM('01'!RC[-54],'02'!RC[-54],'03'!RC[-54])" where '01', '02', '03' are worksheet names in the same workbook. Recording a macro gives the same reference format to other sheets in the workbook. If I create this formula manually, it works after re-opening the workbook. However, if I create the formula with the line above, Excel interprets the '01'!, '02!', '03!' references as those to other workBOOKS instead of workSHEETS. It informs me that the wo...

VBA ActiveCell Range
Hi All, Can't do this simple bit of VBA. Basically, due to various previous macros, you end up at a cell in th worksheet. This cell varies, it could be any cell. I want my macro to remember which cell this is, for instance, A20, suc that I can then refer back to this cell later in the macro. I cant do it. I feel such a fool! Please help df -- Message posted from http://www.ExcelForum.com When you have ended up at that cell, save a variable to it Set oldCell = Activecell Later on you can then refer back to oldCell. -- HTH Bob Phillips ... looking out across Poole Har...

Select contiguous cells by ActiveCell.Interior.ColorIndex and apply borders
I've got some code that loops through a list of start/end dates and fills in cells in each row based on the number of days between those dates (kind of like a Gantt chart) This works fine, but I'd like a way to add borders to the cell range so they stand out a bit better. The difficulty I have is that the borders are currently being added to each individual cell, rather than one border for the whole selection: Each set of selections must stay within the specific row, so I can't have borders applied across multiple rows (hope that makes sense!) Here is the code:Sub Ga...

? offset('activecell',,-2,1,1) in Named Formula ?
I have a cell formula as part of a WorkSheet for explaining stats =prob_T^(A9) * (1-prob_T)^(n-A9) I would prefer to use a Name, k, in place of the A9 relative reference, as this would align the WorkSheet with standard stats names & make explanations easier to understand. This name needs return a reference to the cell at at columnoffset(-2) relative to the cell the Name is used in ... the syntax I've used in the subject line is as close as I've got so far ... ?? is this something that can be made to work? input gratefully received Mat You can define k as: ...

ActiveCell Problem
I can indepently get Trace to work, and Copyrange to print one line at a time. However, I can not both to work in conjunction with each other. wht happens, is the a1..b1 rows, pause for 3 sec and then goes down one row to a2..b2, pauses for two seconds, and then copies to e1..f1. Here is the problem. I now want to copy from e1..f1 to e5..f5 and loop until there is a blank line in column A,B --------------------------------------------------- Public NextTime As Date Sub Trace() Sheet1.Range("A1").Select newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 3 wai...

ActiveCell.Formula
Hi How on earth do I make the following formula to function ? TempNumber=15 ActiveCell.FormulaR1C1 = "= TempNumber*R[-2]C" I have tried val() and different combinations of & and " Hobe somebody will reply. Kind regards Leif Rasmussen One way: TempNumber=15 ActiveCell.FormulaR1C1 = "= " & TempNumber & "*R[-2]C" In article <0c3e01c394c1$40a24080$a401280a@phx.gbl>, "Leif Ramussen" <anonymous@discussions.microsoft.com> wrote: > Hi > How on earth do I make the following formula to function ? > > Temp...

Copy and Paste Formula from ActiveCell to cells in 13 columns prior to the active column
Hi All, I have a sum formula in cell Z30. I am trying to figure out how to copy the sum formula and paste it into the 13 columns immediately before column Z. The trick is that I will not always be starting from column Z and not always on the same row either. At times, it may be column AA10 or AD50 etc so the code need to be able handle this. Any ideas? Thanks, Steve Sub CopyFormula() Set r1 = ActiveCell rr = r1.Row rc = r1.Column Set r2 = Range(Cells(rr, rc - 1), Cells(rr, rc - 13)) r1.Copy r2 End Sub This will copy the active celll to the 13 cells immediate adjacent...

Syntax to save ActiveCell to MyActiveCell then select later
Using 2003 Have been attempting to capture the range (or whatever I need) of an active cell to reselect later. I would like to perform the essence of the code below (but the syntax is not correct) Sub ReturnToOriginalActiveCell() Dim MyActiveCell As Range MyActiveCell = ActiveCell.?????????? [Other VBA Code] MyActiveCell.Select End Sub TIA Dennis Hi try sub foo() dim old_cell as range set old_cell = activecell 'your code old_cell.select end sub -- Regards Frank Kabel Frankfurt, Germany Dennis wrote: > Using 2003 > > Have been attempting to capture the range (or wh...

FormulaR1C1 using variables
I want to do this but can't: ActiveCell.FormulaR1C1 = "=MyFunction(R[variable]C[variable])" What are my options? Try something like ActiveCell.FormulaR1C1 = _ "=MyFunction(R" & CStr(variable) & "C" & CStr(variable) & ")" Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Wed, 21 Apr 2010 13:52:01 -0700, Cheah <Cheah@discussions.microsoft.com> wrote: >I want to do this but can't: > >ActiveCell.Fo...

activecell & Vlookup
Is there a way to rewrite a vlookup based on activecell? =VLOOKUP("activecell",Sheet1!A:B,2,FALSE) where in it picks up value from the active cell? Right click on your worksheet and put something like this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A1") = ActiveCell.Value End Sub change A1 to any blank cell and refer to that cell in your formula. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261 View thi...

VBA? Activecell formatting
Looking for a simple VBA macro for formatting three cells starting wit the active cell. Want to use ctrl-d to select activecell, then next two cells to th right and then fill all three cells with color red -- Message posted from http://www.ExcelForum.com Just a single line of code will do it: Range(ActiveCell, ActiveCell.Offset(0, 2)).Interior.Color = vbRed HTH, Nikos "click4mrh >" <<click4mrh.1bmwdk@excelforum-nospam.com> wrote in message news:click4mrh.1bmwdk@excelforum-nospam.com... > Looking for a simple VBA macro for formatting three cells starting with >...

ActiveCell.Offset Question
Hi, Spreadsheet with many columns: In All column, Row4 I have dates: What I like to do is: "IF" I am in Column y "Any row" I would like to go up to Y4 and copy that value down to another cell . "IF" I am in Column de "Any row" I would like to go up to de4 and copy that value down to another cell . I wrote this thinking it may work, but I keep getting mismatch Type: Sub Look4DATE() Dim mycell As Range Set mycell = ActiveCell.Offset(0, 0) Dim myrange As Range Set myrange = Range(ActiveCell.Offset(0, 0), mycell) myc...

ActiveCell.FormulaR1C1
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...

ActiveCell.Offset for event macro
I need to move from the active cell in any row to the cell in column 6 of the same row. I know how to use ActiveCell.Offset to move a fixed number of rows and columns. But I can't figure out how to handle a relative row and absolute column situtation. What I need is... ActiveCell.Offset(0,"however many cells there are between the column I'm in and column 6) Do I need to create a variable to calculate the number of cells, or is there a simpler way? Thank you! ActiveCell.Offset(0,6 - ActiveCell.Column) ActiveCell.Offset(0,6 - ActiveCell.Column) activesheet.cell...

Activecell.activate dont work
hi everyone!! i need a urgent help on this one. i recieve a excell sheet with macro that is running perfectly in japan. when i recieved it, it halt on the line with activecell.activate which is in the first line of the program. the error says: run-time error '-2147319784(80028018)': Method 'Activate' of the 'Range' failed does this error has to do with my operating system which is win xp prof? or with my office 2000? thank you very much!! and more power to you!!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ ...

Select range from ActiveCell do to Lastcell
From Leith Ross 2/7/2006 Dim EndCell As Range Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp) ActiveSheet.Range(ActiveCell, EndCell).ClearContents If I change the A to an N it selects a range from ActiveCell down to last entry in Column N. My ActiveCell is in Column A but Column N may not have data down to the last cell as in Column A How do I select a range of cells from an ActiveCell in column A across to Column N and down to last data cell in column A -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007...

Select range from ActiveCell do to Lastcell in ColumnA
-- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007 on Mini Mac using VMware. Sub rangefinder() Dim rng As Range Set rng = Range(ActiveCell, Cells(Rows.Count, 1).End(xlUp)) MsgBox rng.Address 'do something with rng End Sub Gord Dibben MS Excel MVP On Mon, 15 Mar 2010 15:23:02 -0700, Aussie Bob C <AussieBobC@discussions.microsoft.com> wrote: Hi Gordon Sorry about double post. I require the range to include across to Column M. My second post states that. -- Thank you Aussie Bob C Little cost to carry...

Show Activecell Date in MsgBox
Can the date in the ActiveCell be shown in a MsgBox, allowing the user to check whether to continue or Cancel the macro running. All data on and below the ActiveCell is cut and pasted into second sheet. -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007 on Mini Mac using VMware. dim resp as long resp = msgbox(Prompt:="Value is: " & format(activecell.value, "mmm dd, yyyy") _ & vblf & "Continue?", buttons:=vbyesno) if resp = vbno then exit sub '??? end if 'do the re...

need help with seeting an activecell in VBA
this is the code i have so far...... Dim rng As Range Application.DisplayAlerts = False Application.ScreenUpdating = False Set wb = Workbooks.Open("f:\Bidditjobs.xls") Set rng = wb.Worksheets("jobs").Columns(1).find(cobOpenPro.Value) ActiveCell.Select Biddit.txtJobNumber.Value = ActiveCell.Value Biddit.cobRep.Value = ActiveCell.Offset(0, 1).Value Biddit.txtMainPrice.Value = ActiveCell.Offset(0, 2).Value Biddit.txtMainPercent.Value = ActiveCell.Offset(0, 3).Value ..... How could I have the ActiveCell.Select begin in the row it finds the cobOpenPro.value? right not is is on...

Excel 2003 - VBA
I'll try to get this as concise as possible. I have a macro that creates a chart and places it on the activesheet ("Param"). I save the name of that chart and store it on the sheet ("Param"), so that I know the name and can delete the chart later when I create another. I recently added a feature to the workbook and have a macro on the "Param" sheet that reacts to change. I use this "change" macro to monitor for a change in a cell that assigns the number of days to display and this works fine under normal operating conditions. When I execute the mac...