storing several variables with amount of variables unknown 1

[kphu]

Greetings,

I created the following script to highlight cells with sales codes.

Sub highlightSalesCode()
Dim SalesCode As Range
Set SalesCode = Range("A1", "A2000&quot
Dim i As Integer
For i = 1 To SalesCode.Cells.Count
If SalesCode.Cells(i).Value Like "0###" Then
SalesCode.Cells(i).Interior.Color = vbYellow
End If
Next i
End Sub

I would like to alter the script so that it would store the value of the sales codes in different variables. The problem is that I have no clue how many sales code would exist in the report at any given time.

Any help would be much appreciated.

 

[ETID]

Just curious ... have you tried Excels' built in conditional formating from the format menu?


No code to write and much cleaner

 

[kphu]

I actually don't care for the fill color of the cell. I'll explain the overall problem then you can get a clearer image of what i'm trying to accomplish.

I currently run and export sales report on a daily basis from crystal report to excel. I have several macros that I run to format and edit the report. One of the macro's break out the report by sales rep and paste it into its own sheet. Each sales rep only gets their sales figures and no one else.

The problem is that if there is a new sales rep/code or if the sales rep leave then I have to edit the macro. I would like to ultimately just run the marcos without much editing.

 

[Zathras]

Why not use an array?

 

[kphu]

Hi Zathras,

I don't now too much about arrays. I'm new to VB. Can you please elaborate?

 

[Zathras]

It's an original feature from GWBASIC (pre-dating VB/VBA). "Dim" stands for dimension and is used to tell the compiler how many instances of a variable to reserve memory for.

Without knowing more about where sales reps are stored in your worksheet and what exactly is the meaning of sales codes, here is about all I can do to illustrate the use of arrays in your situation:

Option Explicit
Dim SalesCodes() As String 'Used by multiple subs in module
Dim xReps As Integer

Sub HighlightSalesCode()
Dim SalesCode As Range
Dim c As Range
Dim i As Integer
  ReDim SalesCodes(5000)
  xReps = 0
  Set SalesCode = Intersect(ActiveSheet.UsedRange, Range("A1:A65536"))
  For Each c In SalesCode
    If c.Value Like "0###" Then
      c.Interior.Color = vbYellow
      SalesCodes(xReps) = c.Value
      xReps = xReps + 1
    End If
  Next c
  Set SalesCode = Nothing
End Sub

Sub showsalescodes()
Dim nX As Integer
  For nX = 0 To xReps - 1
    MsgBox nX & ": " & SalesCodes(nX)
    If nX > 5 Then Exit For
  Next nX
End Sub

I also made a couple of tweaks to the code so you don't have to hard-code the number of rows.

I'm guessing that your sales reps names appear on more than one line. If so, then additional code would be needed to be sure that the name is added to the array only once.

Another way to go is to use a Collection object instead of an array. You could take a look in the help file and decide whether you want to go in that direction. It's pretty much all the same to me. An advantage is that you can simply use the .Add method and trap the error that occurs when a duplicate add is attempted. With arrays, you need code.

 

[kphu]

Thanks Zathras,

I'll workout what you wrote and see if it can help me. I've read some of your other responses in other posts and your definitely an expert. I hope to someday be as knowledgeable as you. Any tips on learning this stuff quicker would be much appreciated.

Thanks!!!

 

[Cresby]

I use ReDim in VB6 is it allowable in VBA? You could then switch from sheet to sheet (rep to rep) without leaving the macro.

ReDim can be used anywhere as long as the array is declared before use. ReDim has the endearing property of zeroing all elements at the same time so even if the array size is unaltered it clears old data in one operation. You never know when you are going to forget that old data is lurking - it has bit me enough times - but not now!

calling a private Subroutine with a local array is effectively the same scenario.