Excel Programming – VBScript Advanced

Creating a VBScript to update tables from a spreadsheet:

For RowCount = 2 To Worksheets(“Data”).UsedRange.Rows.Count() + 1
If Worksheets(“Data”).Cells(RowCount, 1) = cmbYear.Text Then
For tables = 0 To 2
If Worksheets(“Data”).Cells(RowCount, 6) = servicetypes(tables) Then
For brands = 0 To 4
If Worksheets(“Data”).Cells(RowCount, 5) = arrbrands(brands) Then
For salestage = 0 To 2
If CInt(Left(Worksheets(“Data”).Cells(RowCount, 9), 1)) > 0 Then
Month(tables, brands, salestage) = Month(tables, brands, salestage) + Worksheets(“Data”).Cells(RowCount, salestage + 21)
Worksheets(“FCQ1”).Cells(months(tables, salestage), brands + 4) = Month(tables, brands, salestage)
For totalmonthscount = 0 To 2
totalmonthsarr(tables, brands, salestage, totalmonthscount) = totalmonthsarr(tables, brands, salestage, totalmonthscount) + Worksheets(“Data”).Cells(RowCount, totalmonths(salestage, totalmonthscount))
Worksheets(“FCQ1”).Cells((months(tables, salestage)) + totalmonthscount, brands + 4) = totalmonthsarr(tables, brands, salestage, totalmonthscount)
Next
End If
Next
End If
Next
End If
Next
End If
Next

For RowCount = 2 To Worksheets(“Data”).UsedRange.Rows.Count() + 1        If Worksheets(“Data”).Cells(RowCount, 1) = cmbYear.Text Then            For tables = 0 To 2                If Worksheets(“Data”).Cells(RowCount, 6) = servicetypes(tables) Then                    For brands = 0 To 4                        If Worksheets(“Data”).Cells(RowCount, 5) = arrbrands(brands) Then                            For salestage = 0 To 2                                If CInt(Left(Worksheets(“Data”).Cells(RowCount, 9), 1)) > 0 Then                                        Month(tables, brands, salestage) = Month(tables, brands, salestage) + Worksheets(“Data”).Cells(RowCount, salestage + 21)                                        Worksheets(“FCQ1”).Cells(months(tables, salestage), brands + 4) = Month(tables, brands, salestage)                                 For totalmonthscount = 0 To 2                                        totalmonthsarr(tables, brands, salestage, totalmonthscount) = totalmonthsarr(tables, brands, salestage, totalmonthscount) + Worksheets(“Data”).Cells(RowCount, totalmonths(salestage, totalmonthscount))                                        Worksheets(“FCQ1”).Cells((months(tables, salestage)) + totalmonthscount, brands + 4) = totalmonthsarr(tables, brands, salestage, totalmonthscount)                                 Next                                End If                            Next                         End If                    Next                End If            Next        End If    Next

Category: VBScript     Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: