mdeibel
07-05-2011, 10:44 AM
I am trying to make a macro that reads data from an excel spreadsheet and then makes a table that will make use of the data read in from the excel spreadsheet.
To be more precise, I need to search through one column for certain numbers that represent account numbers (i.e. 130, 151, 140, etc...). For each account number found, the macro should read in the $ value pertaining to that account number which is located 1 column to the left (i.e. N4 contains 155, then M4 contains the associated $ amount). As it continues to find account numbers it needs to total all the $ associated with each account number. Also it must keep track of how many different accounts are present in the excel spreadsheet.
I am re-using code previously made from somebody before me, but I'm not 100% sure how it works. When I run this, I cannot test to see if it is working, because I don't know the code to have VBA "print" the value of a variable into a cell so I can see what that variable contains after the macro runs. The code I have is as follows, can somebody verify this should work and please teach me how to print the value of the variable into a given cell? Thank you,
**begin quote
'Search in each array for matches, write the corresponding account number in the account number column
'add subtotals for each sub account numbers and get counts for each sub account item.
Dim cell As Range, rng As Range
Set rng = Intersect(Range("F:F"), ActiveSheet.UsedRange)
For Each cell In rng
cellnumi = cellnum
'Search for Account 130
If InStr(1, cell, "130", 1) Then
If cellnumi <= cellnum Then
tot130 = tot130 + cell.Offset(0, -1)
cellnumi = cellnumi + 1
Cnt130 = Cnt130 + 1
End If
End If
'Search for Account 140
If InStr(1, cell, "140", 1) Then
If cellnumi <= cellnum Then
tot140 = tot140 + cell.Offset(0, -1)
cellnumi = cellnumi + 1
Cnt140 = Cnt140 + 1
End If
End If
'Search for Account 151
If InStr(1, cell, "151", 1) Then
If cellnumi <= cellnum Then
tot151 = tot151 + cell.Offset(0, -1)
cellnumi = cellnumi + 1
Cnt151 = Cnt151 + 1
End If
End If
If cell.Offset(0, q + 1) = "" Then
cell.Offset(0, s) = ("-")
End If
If cell.Offset(0, q).Interior.ColorIndex = 6 Then
cell.Offset(0, s).ClearContents
End If
p = p + 1
cellnum = cellnum + 1
Next
'-----------------------------------------------------------------------------------
'sum each account and get percentages
'Find total value and store it.
sortcell = "P" & p
Range(sortcell).Select
mattotal = ActiveCell.value
xxx = p
'Sort account numbers and costs
p = p - 1
sortcell = "A2:Q" & p
Range(sortcell).Sort Key1:=Range("P2"), Order1:=xlDescending
Range(sortcell).Sort Key1:=Range("Q2"), Order1:=xlAscending
'Declare Variables
p = 0
s = 0
q = 0
'Get count of account numbers found to find percentages
If Cnt130 > 0 Then
AcctCnt = AcctCnt + 1
End If
If Cnt140 > 0 Then
AcctCnt = AcctCnt + 1
End If
If Cnt151 > 0 Then
AcctCnt = AcctCnt + 1
End If
**end quote
instead of searching one column I believe this code searches the entire spreadsheet for account numbers (I'd like to change this to just search one column)
Matt
To be more precise, I need to search through one column for certain numbers that represent account numbers (i.e. 130, 151, 140, etc...). For each account number found, the macro should read in the $ value pertaining to that account number which is located 1 column to the left (i.e. N4 contains 155, then M4 contains the associated $ amount). As it continues to find account numbers it needs to total all the $ associated with each account number. Also it must keep track of how many different accounts are present in the excel spreadsheet.
I am re-using code previously made from somebody before me, but I'm not 100% sure how it works. When I run this, I cannot test to see if it is working, because I don't know the code to have VBA "print" the value of a variable into a cell so I can see what that variable contains after the macro runs. The code I have is as follows, can somebody verify this should work and please teach me how to print the value of the variable into a given cell? Thank you,
**begin quote
'Search in each array for matches, write the corresponding account number in the account number column
'add subtotals for each sub account numbers and get counts for each sub account item.
Dim cell As Range, rng As Range
Set rng = Intersect(Range("F:F"), ActiveSheet.UsedRange)
For Each cell In rng
cellnumi = cellnum
'Search for Account 130
If InStr(1, cell, "130", 1) Then
If cellnumi <= cellnum Then
tot130 = tot130 + cell.Offset(0, -1)
cellnumi = cellnumi + 1
Cnt130 = Cnt130 + 1
End If
End If
'Search for Account 140
If InStr(1, cell, "140", 1) Then
If cellnumi <= cellnum Then
tot140 = tot140 + cell.Offset(0, -1)
cellnumi = cellnumi + 1
Cnt140 = Cnt140 + 1
End If
End If
'Search for Account 151
If InStr(1, cell, "151", 1) Then
If cellnumi <= cellnum Then
tot151 = tot151 + cell.Offset(0, -1)
cellnumi = cellnumi + 1
Cnt151 = Cnt151 + 1
End If
End If
If cell.Offset(0, q + 1) = "" Then
cell.Offset(0, s) = ("-")
End If
If cell.Offset(0, q).Interior.ColorIndex = 6 Then
cell.Offset(0, s).ClearContents
End If
p = p + 1
cellnum = cellnum + 1
Next
'-----------------------------------------------------------------------------------
'sum each account and get percentages
'Find total value and store it.
sortcell = "P" & p
Range(sortcell).Select
mattotal = ActiveCell.value
xxx = p
'Sort account numbers and costs
p = p - 1
sortcell = "A2:Q" & p
Range(sortcell).Sort Key1:=Range("P2"), Order1:=xlDescending
Range(sortcell).Sort Key1:=Range("Q2"), Order1:=xlAscending
'Declare Variables
p = 0
s = 0
q = 0
'Get count of account numbers found to find percentages
If Cnt130 > 0 Then
AcctCnt = AcctCnt + 1
End If
If Cnt140 > 0 Then
AcctCnt = AcctCnt + 1
End If
If Cnt151 > 0 Then
AcctCnt = AcctCnt + 1
End If
**end quote
instead of searching one column I believe this code searches the entire spreadsheet for account numbers (I'd like to change this to just search one column)
Matt