Excel VBA setting cell value to a formula

Question

Hey Everyone I have some hard code in excel vba that will set a given cells value = to a formula and i have multiple lines of code that does this for each cells value but changes the formula to look at the specific column its under.

So my question is, is there any way to code the stuff below in 1 line so that the formula will automatically change the columns its looking at depending on the column its actually in.

Sheets("Standalone Non Clustered").Range("G10").Formula = "=CEILING(Worksheet!$G$9*(1+Worksheet!$G$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("H10").Formula = "=CEILING(Worksheet!$H$9*(1+Worksheet!$H$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("I10").Formula = "=CEILING(Worksheet!$I$9*(1+Worksheet!$I$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("J10").Formula = "=CEILING(Worksheet!$J$9*(1+Worksheet!$J$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("K10").Formula = "=CEILING(Worksheet!$K$9*(1+Worksheet!$K$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("L10").Formula = "=CEILING(Worksheet!$L$9*(1+Worksheet!$L$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("M10").Formula = "=CEILING(Worksheet!$M$9*(1+Worksheet!$M$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("N10").Formula = "=CEILING(Worksheet!$N$9*(1+Worksheet!$N$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("O10").Formula = "=CEILING(Worksheet!$O$9*(1+Worksheet!$O$10+Info!$B$14),5)"
    Sheets("Standalone Non Clustered").Range("P10").Formula = "=CEILING(Worksheet!$P$9*(1+Worksheet!$P$10+Info!$B$14),5)"

See how each range goes from G to P in the alphabet and the formula in each line is adjusted for that G to P column. Is there a way to have one line for Range("G10:P10") and have the formula change itself based on what column its actually in on the spreadsheet?

Edit:

Removing the $ syntax fixed the problem but I am coming up with another problem:

Say I have the range set to a variable and I want to add a row to that range variable and use that variable in the formula line above. How would i go about incrementing that variable by 1.

        Set shownDatabaseRows = Sheets("Standalone Non Clustered").Range("10:10")
Set hiddenDatabaseRows = Sheets("Standalone Non Clustered").Range("11:108")

hiddenDatabaseRows.EntireRow.Hidden = True
shownDatabaseRows.EntireRow.Hidden = False

    Sheets("Standalone Non Clustered").shownDatabaseRows.Formula = "=CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5)"


For Each cell In rng

    If cell.Value >= 2048 Then


        shownDatabaseRows = shownDatabaseRows.Count + 1

        shownDatabaseRows.EntireRow.Hidden = False

                Sheets("Standalone Non Clustered").shownDatabaseRows.Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/2"

      ...

I am getting an error at the formula lines so im thinking that something is wrong with the way I am setting up the shownDatabaseRows variable. any ideas?

You better start another question. Count is read-only, you can't change it. I am not really understanding what you want. Add a new row to shownDatabaseRows? Set shownDatabaseRows = Union(shownDatabaseRows, cell.EntireRow)? Sheets("Standalone Non Clustered").shownDatabaseRows makes no sense. — LS_ᴅᴇᴠ
– LS_ᴅᴇᴠ, Commented Jul 22, 2013 at 14:01
I started a new question: stackoverflow.com/questions/17789077/… — user1305569
– user1305569, Commented Jul 22, 2013 at 14:58

LS_ᴅᴇᴠ · Accepted Answer · 2013-07-22 13:00:58Z

4

Sheets("Standalone Non Clustered").Range("G10:P10").Formula = "=CEILING(Worksheet!R9C[0]*(1+Worksheet!R10C[0]+Info!$B$14),5)"

answered Jul 22, 2013 at 13:00

LS_ᴅᴇᴠ

11.2k1 gold badge27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1305569 Over a year ago

removing the $ worked, but having a problem with range variable check my edit

Community · Accepted Answer · 2017-05-23 12:21:37Z

2

You can always set the formula for a range such as G10:P10 in your case

Sheets("Standalone Non Clustered").Range("G10:P10").Formula = "=CEILING(Worksheet!G9*(1+Worksheet!G10+Info!$B$14),5)"

just remove the $ from the cell references which should be dynamically changed.

Also you can follow Set formula to a range of cells for further clarification.

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Jul 22, 2013 at 12:58

AKS

20k3 gold badges47 silver badges55 bronze badges

2 Comments

user1305569 Over a year ago

Ok so just removing the $ will have the formula auto change even though its setting every row = the formula with G as the column?

AKS Over a year ago

As I can see in Set shownDatabaseRows = Sheets("Standalone Non Clustered").Range("10:10"), you haven't mentioned the column, so I am not sure what will you achieve through Sheets("Standalone Non Clustered").shownDatabaseRows.Formula = "=CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5)".

Vasim · Accepted Answer · 2013-07-22 12:57:00Z

0

Something like....

Sheets("Standalone Non Clustered").Range("G10").Formula = "=CEILING(Worksheet!$G$9*(1+Worksheet!$G$10+Info!$B$14),5)"
Sheets("Standalone Non Clustered").Range("G10:P10").FillRight

answered Jul 22, 2013 at 12:57

Vasim

3,1636 gold badges39 silver badges58 bronze badges

Collectives™ on Stack Overflow

Excel VBA setting cell value to a formula

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related