Search code examples
excelvbarangecellformula

Excel VBA setting cell value to a formula

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?

Solution

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