0

I'm trying to make a program in the Excel VBA that inserts a formula into a column of cells. This formula changes based on the contents of the cell directly to the left. This is the code I have written so far:

Sub Formula()
Dim colvar As Integer
colvar = 1
Dim Name As String
Name = "Sample, J."
Do While colvar <= 26
    colvar = colvar + 1
    Name = Range("B" & colvar).Value
    Range("C" & colvar).Value = "='" & Name & "'!N18"

Loop
End Sub

As you can see, I want to insert the variable Name between the formula strings, but Excel refuses to run the code, giving me a "application-defined or object-defined error."

Is there a way to fix this?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Greedav
  • 11
  • 1
  • 1
  • 3
    a) You are going to have to supply the values in `Range("B" & colvar).Value` that are causing the error. b) The next line should be `Range("C" & colvar).FORMULA = "='" & Name & "'!N18"`. –  Sep 05 '15 at 01:51
  • 1
    Most likely, when you see the error, the contents of `Name` is not a valid worksheet name. – Ron Rosenfeld Sep 05 '15 at 02:27
  • @Jeeped I have not seen where, when **writing** a formula to a cell, that using the `.Formula` vs `.Value` property makes a difference. Obviously it does when reading the formula. Do you have an example where a different result occurs? I've heard there may be issues with non-US format dates in variant arrays, but I've not tested that. – Ron Rosenfeld Sep 05 '15 at 02:41
  • 1
    @RonRosenfeld - No, the `Value` can be used to stuff a formula into a cell just as `.Formula` can be used to put a value into a cell. `.Formula` is the better method but not mission critical (which was why I relegated it to b) in my response). –  Sep 05 '15 at 02:49
  • @Jeeped Thanks. It adds some clarity to the code, but I could not find any other reason. I read [this SO thread](http://stackoverflow.com/questions/13687800/why-use-range-formula-in-vba-vbe-for-excel-2003-instead-of-range-value) on the topic, but didn't understand the answer. I think the responder is saying something similar to read vs write, but not sure. – Ron Rosenfeld Sep 05 '15 at 02:52

2 Answers2

0

You will need some error checking in case the sheets don't actually exist in the workbook.

it looks like you are looping through column B that has a list of sheet names and want range N18 to display next to it.

Something like

    Sub Button1_Click()
    Dim Lstrw As Long, rng As Range, c As Range
    Dim Name As String

    Lstrw = Cells(Rows.Count, "B").End(xlUp).Row
    Set rng = Range("B1:B" & Lstrw)

    For Each c In rng.Cells
        Name = c
        c.Offset(, 1) = "='" & Name & "'!N18"
    Next c

End Sub

Or you can just list the sheets and show N18 next to it, run this code in a Sheet named "Sheet1"

    Sub GetTheSh()
    Dim sh As Worksheet, ws As Worksheet

    Set ws = Sheets("Sheet1")

    For Each sh In Sheets

        If sh.Name <> ws.Name Then

            ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1) = sh.Name
            ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(0, 1) = sh.Range("N18")

        End If

    Next sh


End Sub
Davesexcel
  • 6,896
  • 2
  • 27
  • 42
0

I actually found that I had just made a silly error: the line Do While colvar<=26 should have been Do While colvar<26. The cells were being filled, but the error manifested because one cell was being filled by a nonexistent object.

I did decide to use the .Formula property rather than .Value.

Enigmativity
  • 113,464
  • 11
  • 89
  • 172
Greedav
  • 11
  • 1
  • 1