 |
| 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
|
|
|
|
February 27th, 2005, 10:52 AM
|
|
Authorized User
|
|
Join Date: Oct 2004
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
February 28th, 2005, 04:34 PM
|
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
March 1st, 2005, 08:52 PM
|
|
Authorized User
|
|
Join Date: Oct 2004
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
March 3rd, 2005, 10:06 AM
|
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
|
