VBA: code runs but no output is generated

Question

I have an excel workbook with two sheets, named "Dashboard Supplier View' and "ComputingSolutions". In the dashboard supplier view tab, the user has to write down a value in the named cell "categoryName". When the user writes down "Computing Solutions" in this named cell and runs the code, I want the code to go to the ComputingSolutions sheet and to store all the suppliernames in column A in that sheet into an array, to then print out all suppliernames in the original sheet Dashboard Supplier View.

The "n" variable refers to a named cell on the ComputingSolutions sheet and is the number of suppliers on that sheet. (This number is written down already, the user does not need to change this)

When I run the code, nothing shows up. Nothing is printed on the dashboard supplier view sheet at the designated location. I get no error either. I checked all names and everything is spelled correctly. I'm not sure what could be the issue, but I suspect it has something to do with the activation of the worksheets.

Public Sub DashboardSupplier()

Dim category As String
Dim supplierArray() As String
Dim c As Integer

Worksheets("Dashboard Supplier View").Activate

category = Names("categoryName").Value

'Suppliers for selectec category

If category = "Computing Solutions" Then

Worksheets("ComputingSolutions").Activate

n = Names("supplierAmount").Value

ReDim supplierArray(1 To n) As String

    For c = 1 To n

    supplierArray(c) = Cells(3 + c, 1)

    Next c

Worksheets("Dashboard Supplier View").Activate

    For c = 1 To n

    Cells(6 + c, 4) = supplierArray(c)

    Next c

   End If

   End Sub

how does the n variable relate to a named cell? Is the cell named n? — Andy Wynn
– Andy Wynn, Commented Aug 1, 2016 at 15:48
Have you tried debugging? Put a break (eg) inside the If category = "Computing Solutions" Then block - does your code reach that point? — Tim Williams
– Tim Williams, Commented Aug 1, 2016 at 15:50
n refers to the named call supplierAmount via n = Names("supplierAmount").Value — Jolien .A
– Jolien .A, Commented Aug 1, 2016 at 15:50

Andy Wynn · Accepted Answer · 2016-08-01 16:21:52Z

2

OK, I think I have a solution to this because it was bothering me. I set up your environment as a test, and found that for some reason, Names("categoryName").Value was returning the cell address, i.e. it was returning Names("categoryName").RefersTo. Try swapping the .value part for .RefersToRange.Value and see if you have better luck. Here is my working formatted code:

Public Sub DashboardSupplier()
    Dim category As String
    Dim supplierArray() As String
    Dim c As Integer
    Dim n As Long
    Worksheets("Dashboard Supplier View").Activate
    category = Range("categoryName").Value
    'Suppliers for selectec category
    If category = "Computing Solutions" Then
        Worksheets("ComputingSolutions").Activate
        n = CLng(Range("supplierAmount").Text)
        ReDim supplierArray(1 To n) As String
        For c = 1 To n
            supplierArray(c) = Cells(3 + c, 1)
        Next c
        Worksheets("Dashboard Supplier View").Activate
        For c = 1 To n
            Cells(6 + c, 4) = supplierArray(c)
        Next c
    End If
End Sub

Unfortunately, I have no explanation for this behavior.

edited Aug 1, 2016 at 16:21

answered Aug 1, 2016 at 16:09

Andy Wynn

1,3038 silver badges15 bronze badges

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

9 Comments

Jolien .A Over a year ago

This gives me Run-Time error 1004:Application-defined or object-defined error. I copied your entire code and used it. I'm baffled

Andy Wynn Over a year ago

F8 through it and tell me where that pops up.

Jolien .A Over a year ago

Here: n = Names("supplierAmount").RefersToRange.Value

Andy Wynn Over a year ago

What version of Office are you using?

simpLE MAn Over a year ago

If you just change Names by Range you'll get the value contained in that named range cell.

|

Collectives™ on Stack Overflow

VBA: code runs but no output is generated

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related