CODEWITHSUNDEEP
vbaexcel
Jolien .A
Jolien .A

Reputation: 173

VBA: code runs but no output is generated

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

Upvotes: 0

Views: 1771

Answers (1)

Andy Wynn
Andy Wynn

Reputation: 1273

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.

Upvotes: 2

Related Questions