Basically, I receive information about a CDMA system in an excel format.
It contains a fairly determined breakdown of the information on usage over a given time frame, further broken down by sector and CDMA carrier.
Being lazy, I got tired of manually creating the graphs, and filtering the info to make the graphs. So now here we are with myself in way over my head once again.
Manually, I would take the raw data from the "Raw Data" Sheet and filter it based upon "sector" (which will always be alpha, beta and gamma), and Carrier. For each carrier, I make a separate worksheet (for example F1, for the first carrier), where I paste the filtered information from the raw data sheet based on the sector.
the final result has each sector in a column, with only the filtered raw data for that particular carrier. I repeat this as needed for every carrier.
I want to make this macro sufficiently generic so that I can use this on different cell sites other than the one I am currently working on. Seeing as one of my criteria is the quantity of CDMA carriers, I know that I have to do a loop with a counter where the integers coincide with the carrier I am filtering. The lengths of the columns to be filtered are also various, as I may get my data with a different time frame selected. (I may want to trend over 30 days instead of 15 days)
I know that I could use the two functions max (to get the highest carrier count) and count (to find how many rows of data I have) I don't mind doing these independently, and storing their values on the raw data page each in their individual cell. The problem I am having is with the loop syntax, and using the current iteration (which is the current carrier) as a variable for naming the worksheet. I will also need to pass the count number in the sub so I know how many rows to copy from the "raw data" sheet to the carrier sheet.
Here's what I have so far:
Dim carrier as Integer
carrier = 0
Do Until Carrier = Range.Cells(this is the cell where I would put my value from my MAX function)
carrier = carrier + 1
Loop
Sheets.Add.Name = "I would like to put the current iteration integer here with some added text"
Range ("B1").Select
ActiveCell.FormulaR1C1 = "alpha"
ActiveCell.FormulaR2C1 = "beta"
ActiveCell.FormulaR3C1 = "gamma"
Sheets("raw_data").Select
Selection.AutoFilter Field:=4, Criteria1:="1"
Selection.AutoFilter Field:=3, Criteria1:="1"
Range("G2:value of COUNT function here").Select
.......
It continues, but it's just filtering the data and pasting it to the proper worksheet.
I did try to look some of this up on the internet, but I found myself a little confused as to the looping syntax. I may even be on the wrong path altogether here, if so could someone point me to an online tutorial (the ones I looked at just told me to use the macro recorder)
It contains a fairly determined breakdown of the information on usage over a given time frame, further broken down by sector and CDMA carrier.
Being lazy, I got tired of manually creating the graphs, and filtering the info to make the graphs. So now here we are with myself in way over my head once again.
Manually, I would take the raw data from the "Raw Data" Sheet and filter it based upon "sector" (which will always be alpha, beta and gamma), and Carrier. For each carrier, I make a separate worksheet (for example F1, for the first carrier), where I paste the filtered information from the raw data sheet based on the sector.
the final result has each sector in a column, with only the filtered raw data for that particular carrier. I repeat this as needed for every carrier.
I want to make this macro sufficiently generic so that I can use this on different cell sites other than the one I am currently working on. Seeing as one of my criteria is the quantity of CDMA carriers, I know that I have to do a loop with a counter where the integers coincide with the carrier I am filtering. The lengths of the columns to be filtered are also various, as I may get my data with a different time frame selected. (I may want to trend over 30 days instead of 15 days)
I know that I could use the two functions max (to get the highest carrier count) and count (to find how many rows of data I have) I don't mind doing these independently, and storing their values on the raw data page each in their individual cell. The problem I am having is with the loop syntax, and using the current iteration (which is the current carrier) as a variable for naming the worksheet. I will also need to pass the count number in the sub so I know how many rows to copy from the "raw data" sheet to the carrier sheet.
Here's what I have so far:
Dim carrier as Integer
carrier = 0
Do Until Carrier = Range.Cells(this is the cell where I would put my value from my MAX function)
carrier = carrier + 1
Loop
Sheets.Add.Name = "I would like to put the current iteration integer here with some added text"
Range ("B1").Select
ActiveCell.FormulaR1C1 = "alpha"
ActiveCell.FormulaR2C1 = "beta"
ActiveCell.FormulaR3C1 = "gamma"
Sheets("raw_data").Select
Selection.AutoFilter Field:=4, Criteria1:="1"
Selection.AutoFilter Field:=3, Criteria1:="1"
Range("G2:value of COUNT function here").Select
.......
It continues, but it's just filtering the data and pasting it to the proper worksheet.
I did try to look some of this up on the internet, but I found myself a little confused as to the looping syntax. I may even be on the wrong path altogether here, if so could someone point me to an online tutorial (the ones I looked at just told me to use the macro recorder)
Facebook
Twitter