Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 27th, 2005, 10:52 AM
Authorized User
Points: 96, Level: 1
Points: 96, Level: 1 Points: 96, Level: 1 Points: 96, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Inserting formula into active cell

I need to insert a formula into a series of active cells. The formula references a particular cell on another sheet.The cell addresses of the formula will have to change as I add rows to the other sheet. The correct answer places the following formula into the active cell(c2):

=IF(QS!J2<>"","Done",(IF(QS!H2<>"","Chk Rqst",IF(QS!C2<>"","BSA",""))))

and when rowcount = rowcount +27 (ie 29)

=IF(QS!J29<>"","Done",(IF(QS!H29<>"","Chk Rqst",IF(QS!C29<>"","BSA",""))))

I have tried the following formats (rowcount = 2):

selection.formula = ""=IF(QS!J" & rowcount & "<>"""",""Done"",(IF(QS!H" & rowcount & "<>"""",""Chk Rqst"",IF(QS!C" & rowcount & "<>"""",""BSA"",""""))))""""

Also fstring = "=IF(QS!J" & rowcount & "<>"""",""Done"",(IF(QS!H" & rowcount & "<>"""",""Chk Rqst"",IF(QS!C" & rowcount & "<>"""",""BSA"",""""))))"""
selection.formula = fstring

and also variations on the r1C1 coding format. I seem to get a variety of errors from each approach.
Would appreciate some help in how I can approach this problem.
Alison





 
Old February 28th, 2005, 04:34 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

 ActiveCell.FormulaR1C1 = _
        "=IF(QS!R[1]C[9]<>"""",""Done"",(IF(QS!R[1]C[7]<>"""",""Chk Rqst"",IF(QS!R[1]C[2]<>"""",""BSA"",""""))))"

this should do the trick...The key to recording this type of macro is to set your recorder to Relative referencing rather than Absolute referencing. This is selected once you have started your recorder the two icons appear, one being the stop button, the other being the toggle for absolute referecing.

Cheers


Matthew

 
Old March 1st, 2005, 08:52 PM
Authorized User
Points: 96, Level: 1
Points: 96, Level: 1 Points: 96, Level: 1 Points: 96, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Matthew, thanks for the help. What I'm having a problem with is that I have to be able to manipulate the R[]C[] reference. The reference depends on how many items are in the list and so the r[x]c[x]has to be dynamic, where x is equal to 29 or 30 or x+5, for example.

 
Old March 3rd, 2005, 10:06 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think I understand your request, if this doesn't answer the problem I might ask to look at the worksheet via email.


myRow = 29
ActiveCell.FormulaR1C1 = "=IF(QS!R" & myRow & "C10<>"""",""Done"",(IF(QS!R" & myRow & "C8<>"""",""Chk Rqst"",IF(QS!R" & myRow & "C3<>"""",""BSA"",""""))))"

You can define the rowcount in a varienty of ways, including by counting the number of items in the list. One way you could define the number of rows in a list is...

Assuming the list is set to Cells A1 to A15...

dim myRow as integer

myRow = Range("A65000",selection.end(xlup)).row

similarly you could define the column in the same way...

Dim myCol as integer

myCol = Range("IV1",selection.end(xltoleft)).column

just shout with any Q?'s

Cheers

Matthew





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel putting result of a formula in another Cell hakimk Excel VBA 8 July 29th, 2016 12:35 AM
Using Formula on Active Cell manojkumarsoni Excel VBA 3 August 26th, 2007 08:56 AM
What would the formula "=+D4" do in a cell? BrianWren Excel VBA 2 February 2nd, 2006 03:36 AM
Inserting formula slgknjn Excel VBA 3 January 25th, 2006 03:52 AM
Cell returns its own formula after a macro! timoma Excel VBA 2 December 19th, 2004 03:48 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.