0

I'm a beginner in VBA and so would appreciate some help. I'm trying to build a directory to display results based on user selection of 'category' and 'subcategory'. When I run the script, there is no error message but I don't see any results displayed. Is there anything wrong with my code?

Sub Button5_Click()

'''Function to search data set

Dim wkbk As Workbook
Dim row_data As Range
Dim results_ws As Worksheet, data_ws As Worksheet
Dim n_row As Integer '''counter for row number
Dim main_ws As Worksheet
Dim category As String, subcategory As String

Set wkbk = ThisWorkbook
Set results_ws = wkbk.Sheets("Demo_Results")
Set data_ws = wkbk.Sheets("Demo_Data")
Set main_ws = ThisWorkbook.Sheets("Demo_Main")

'''Clear the results worksheet
results_ws.Cells.ClearContents
results_ws.Hyperlinks.Delete
results_ws.Cells.Font.Underline = False
results_ws.Columns("B:E").Font.Color = RGB(0, 0, 0)

category = main_ws.Cells(13, "F").Value
subcategory = main_ws.Cells(15, "F").Value
result_count = 0

final_data_row = data_ws.Range("A1000").End(xlUp).Row

For n_row = 2 To final_data_row:
    If data_ws.Cells(n_row, "A") = category And data_ws.Cells(n_row, "B") = subcategory Then
        With data_ws
            Set row_data = data_ws.Range(data_ws.Cells(n, 1), data_ws.Cells(n, 7))
            result_count = result_count + 1
            results_ws.Cells(Rows.Count, "E").End(xlUp).Offset(2, -3).Value = Str(result_count) + ".)"
            results_ws.Cells(Rows.Count, "E").End(xlUp).Offset(2, -2).Value = row_data.Cells(1, 3).Value

            '''Enter state
            results_ws.Cells(Rows.Count, "C").End(xlUp).Offset(1, 1).Value = "State:"
            results_ws.Cells(Rows.Count, "D").End(xlUp).Offset(0, 1).Value = row_data.Cells(1, 4).Value
        End With
    End If
Next n_row

'''Format table
Sheets("Demo_Results").Select
Call ResetFormatting
ActiveWindow.ScrollRow = 1

End Sub

Function ResetFormatting():
'''Resent font style and size for results column
Dim wsheet As Worksheet
Set wsheet = ThisWorkbook.Sheets("Demo_Results")

wsheet.Cells.Font.Name = "Verdana"
wsheet.Cells.Font.Size = 11

End Function
user10332864
  • 1
  • 1
    Try stepping through your code to find the issue: http://www.cpearson.com/excel/DebuggingVBA.aspx Also be sure to check out the [tour] (you'll earn your first badge!) and see the [help/on-topic] for more information about what's on topic on this site. If, after [*doing more research*](//meta.stackoverflow.com/q/261592), you have a *specific* problem, you can [edit] your post to share your **[mcve]** as well as sample(s) of your data and some background info. Here's a [checklist](//codeblog.jonskeet.uk/stack-overflow-question-checklist/) and there are more more tips in: "[ask]". – ashleedawg Sep 07 '18 at 23:35
  • Hi, Welcome to the stackoverflow community! There are several methods to debug code, a useful source to start from is: [debugging vba](https://stackoverflow.com/questions/50189158/debugging-vba-locating-problems-and-troubleshooting-methods). In VBA go to "View" -> "Locals Window". Then run through your code line by line using F8 key. In the local window you can now see how your objects and parameters will be assigned values when the code is run. – Wizhi Sep 08 '18 at 08:26

0 Answers0