Constants or Variables & how to use them to populate a cell?

[Upex]

Hi All,

I have a sheet that has circa 300 constants/variables that I'll use throughout various bits codes to look for specific entries and I'm unsure how best to approach it.

I want to define all 300 in one place, so I can then refer to them, but unsure if they would be better as variables, or constants.

E.g.

Variable1 = "Ant"
Variable2 = "Anteater"
...
Variable300 = "Zebra"

or

Constant1 = "Ant"
...
Constant2 = "Zebra"

Once I've defined them, I'll want a way to produce these as a list, but unsure how to call them

E.G.

Col.A (typed reference) Col.B(code to add the corresponding value in this column, based upon the text in Col.A)
Constant1 Ant
Constant2 Anteater
...
Constant300 Zebra

I've been trying the 'Public Const' way of defining them (in a module):

Public Const Constant1 = "Ant"
Public Const Constant2 = "Anteater"
Public Const Constant300 = "Zebra"

and the below code to pull the values, but alas, it doesn't work:

Sub Populate_Constant_List()
Dim intRowCount As Integer
Dim I As Integer
intRowCount = Range("a1").CurrentRegion.Rows.Count - 3
Range("b4").Select
For I = 1 To intRowCount
ActiveCell.Value = constant & I
ActiveCell.Offset(1, 0).Select
Next I
End Sub

Any ideas on how I should best approach this, and how I can get it to populate a list of them all to a sheet (so I don't have to review in VBA & the list can be there for viewing etc)?

Many thanks,

Upex

 

[CodeNinja]

Upex,
I am not sure why you need to create 300 + constants, but you might consider using an array or collection instead...

ie:

Sub arr()
Dim s() As String
Dim i As Integer
ReDim s(1 To 300)
s(1) = "Ant"
s(2) = "Anteater"
s(300) = "Zebra"

For i = 1 To 300
    Sheet1.Cells(i, 1) = s(i)
Next i


End Sub

depending on your need, there may be a LOT easier way to tackle this problem...

CN

 

[Upex]

Thanks Codeninja - but would I need to type in S(3) to s(299) in that code?

What I'm looking for is to be able to have the 300 entries defined, so I can then use the 'code name' - i.e. 'Constant1' in code, rather than having to type the actual 'Ant' each time.

For example, on one sheet, within lots of other code, I have

activecell.value = constant1

rather than having

activecell.value = "Ant"

and elsewhere, I can use the same approach within different code, e.g.

          If sTest Like Constant1 Then
                    Range("b" & c.Row) = "Yes"
                    Range("ak" & c.Row) = Constant1
                    GoTo GotOne
                    End If

What I aim to achieve is all the code - baring the defining of each variable/constant/whatever - will stay the same, so when I want Constant1 to change from Ant to Apple, I can change the definintion of Constant1 and the code will still work without needing to be changed (as there lots of it, and its a nightmare to go through each time one of the values changes.

Apologies if I'm not making much sense here.

Thanks,

Upex

EDIT: Just thought - perhaps using named ranges would work better? i.e. sheet 'Validations' - 'B4' value = "Ant" and I define that cell as 'Variable1' named range, 'B5' named as Variable2 etc. Then use the name as a range within the code? (I'll be 3 rows and 1 column offset due to headers etc, so the named range of 'Variable1' would actually need to be B4, 'Variable 2' as B5 etc?


If so - is there a code/easier way to define the 300 named ranges?
Cheers,

 

[CodeNinja]

If you have those 300 entries somewhere, you can loop through and set them with the array...

Ant
Anteater
Bat

Sub setArr()
Dim s() As String
Dim i As Integer
ReDim s(1 To 300)

For i = 1 To 300
    s(i) = Sheet1.Cells(i, 1)
Next i


End Sub

 

[Upex]

Thanks CodeNinja - I'm playing with that, and so far got it mapping them all, and have played with transposing them both vertically and horizontally which is working.

Two questions though if I may:

If I have s(10) set as "Pickle", can I use this s(10) ref across multipul sheets, and how would I reference the s(10) within code that looks for a match of that value e.g. my present 'hard coded' code is:

           If sTest Like "Pickle" Then
                    Range("b" & c.Row) = "Yes"
                    Range("al" & c.Row) = "Pickle"
                    GoTo GotOne
                End If

Would this simply be a case of using:

           If sTest Like s(10) Then
                    Range("b" & c.Row) = "Yes"
                    Range("al" & c.Row) = s(10)
                    GoTo GotOne
                End If

Are there any considerations I need to make as to where the code sits?

I assume if the code you helped with (as below) that sets the s(#)'s is in a module, then could code on different sheets access that? or do I need to 'call' the below before executing code on sheets, or do I even need to actually add the below code to each sheet?

ie module has:

Sub Set_Refs()
Dim s() As String
Dim i As Integer
ReDim s(1 To 250)
For i = 1 To 250
    s(i) = Sheet14.Cells(i + 1, 2)
Next i
End sub

Then on each sheet that needs to link to the references, start with calling the above, to set them, i.e.

Sheet:

Call Set_Refs

{then my code that uses s(#)'s}

or even

Dim s() As String
Dim i As Integer
ReDim s(1 To 250)
For i = 1 To 250
    s(i) = Sheet14.Cells(i + 1, 2)
Next i

{my code that uses s(#)'s}

Sorry for all the Q's - I've not had chance to try these different approaches out and hope someone can say which is better / will work etc, and save me a little faffing about.

Many thanks,

Upex

 

[CodeNinja]

Ok... First question:
If I have s(10) set as "Pickle", can I use this s(10) ref across multipul sheets, and how would I reference the s(10) within code that looks for a match of that value e.g. my present 'hard coded' code is:

You are correct, you would simply need to test the condition against s(10), so simply replace the string variable with s(10)


Second question:
I assume if the code you helped with (as below) that sets the s(#)'s is in a module, then could code on different sheets access that? or do I need to 'call' the below before executing code on sheets, or do I even need to actually add the below code to each sheet?
The way I wrote the variable is local to the subroutine. So, as long as you are still in the subroutine (or a passed sub from there), the variable will exist and know what the different array elements are... You can reference any sheet you want from there. If, however, you go outside the subroutine (call another sub) you will either have to pass that variable on to the new subroutine or make the variable global.


IE:
set the variable with a call: You can do this 2 ways.... via calling within the original subroutine or via function:
call method:

Sub setArr()
Dim s() As String
Dim i As Integer
ReDim s(1 To 300)

For i = 1 To 300
    s(i) = Sheet1.Cells(i, 1)
Next i
'Now call the subroutine that uses s()
Call useArrS(s)

End Sub

Sub useArrS(s() As String)
'This is an example... it will move through sheet 2 and put the data in the cells in column A
Dim i As Integer
For i = 1 To 300
    Sheet2.Cells(i, 1) = s(i)
Next i

End Sub

To use the function method:

Function setArr() As String() 'Note: changed this to a function
Dim s() As String
Dim i As Integer
ReDim s(1 To 300)

For i = 1 To 300
    s(i) = Sheet1.Cells(i, 1)
Next i

setArr = s

End Function

Sub useArrS()
'This is an example...
Dim i As Integer
'need to create an array here:
Dim str() As String
'set the array by calling the function
str() = setArr()
'use the array:
For i = 1 To 300
    Sheet2.Cells(i, 1) = str(i)
Next i

End Sub

Finally the global variable method:
in this case, I call the two separately, but do not need to pass the variable. I first have to set the values of s(), then I can use it at any time while the program continues to run... Note: I would run "MasterSub". If I ran setArr() by its self, at the end of the subroutine, the variable would be destroyed... If I run useArr() the variable has not yet been set.

Dim s() As String

Sub setArr()

ReDim s(1 To 300)

For i = 1 To 300
    s(i) = Sheet1.Cells(i, 1)
Next i

End Sub

Sub MasterSub()
Call setArr
Call useArrS

End Sub



Sub useArrS()
'This is an example...

For i = 1 To 300
    Sheet2.Cells(i, 1) = s(i)
Next i

End Sub

I hope that helps...
CN.

 

[Upex]

Thanks CN, ill have to try tomorrow, as home now and actually don't have a computer at home amazingly enough.

Although from reading this, I think that if I call 'your code' that sets the s(#)'s, from within the sheet code, it'll pull your code through, set the s(#)'s and then the rest of the sheet code can run whilst using the s(#)'s.

I'll give it a go and post back.

Many thanks for your help with this, its very much appreciated.

Cheers, Upex

 

[CodeNinja]

Upex,
Yep, you can call the code from the sheet code, however, you may need to name the sub as public... Ie:

Public Sub setArr()

ReDim s(1 To 300)

For i = 1 To 300
    s(i) = Sheet1.Cells(i, 1)
Next i

End Sub

 

[Upex]

CN - I've had a look at your different ideas, and to be honest, as tad over my head, so I've just added 'your' code to mine and it works as I need it to - many thanks.

This has however led me to wondering if there is an easier way of doing my comparison. I've added the s(#)'s inplace of actual string values, which is great and will allow for me to not need to update the actual code, rather the spreadsheet - which is ace, but, the code below needs to be created for the 500 s(#)'s (I'm building some fat into the process for future growth - hence the increase from 300) and I wonder if there is an easier process?

Dim r As Range
    Dim c As Range
    Dim i As Integer
    Dim x As Integer
    Dim sColumn As String
    Dim vSplit As Variant
    Dim sTest As String
    
    Dim s() As String
    ReDim s(1 To 500)
    
    For i = 1 To 500
        s(i) = Sheet14.Cells(i + 1, 2)
    Next i

    For i = 1 To 2
        If i = 1 Then
            sColumn = "J"
        Else
            sColumn = "Y"
        End If
        Set r = Range(Range(sColumn & 3), Range(sColumn & Sheet6.Rows.Count).End(xlUp))
        For Each c In r.Cells
            vSplit = Split(c, " ")
            For x = LBound(vSplit) To UBound(vSplit)
                sTest = Replace(vSplit(x), ",", "")

                If sTest Like s(1) Then                 
                    Range("b" & c.Row) = "Yes"          
                    Range("ag" & c.Row) = s(1)          
                    GoTo GotOne
                End If
                If sTest Like s(2) Then
                    Range("b" & c.Row) = "Yes"
                    Range("ah" & c.Row) = s(2)
                    GoTo GotOne
                End If
                If sTest Like s(3) Then
                    Range("b" & c.Row) = "Yes"
                    Range("ai" & c.Row) = s(3)
                    GoTo GotOne
                End If

'.......

                If sTest Like s(500) Then
                    Range("b" & c.Row) = "Yes"
                    Range("tl" & c.Row) = s(500)
                    GoTo GotOne
                End If

Basically what the above does (which I'm sure you can read from the code - albeit likely quiet convoluted in method) is that it will check for occurance of s(1) in the rows J & Y cells, then if it finds a match in J or Y, it adds the s(1) value to the rows AG cell, then moves to the next.

The checking of the text in J & Y is always the same as the trigger cells, and the data to check always starts in row 4 (rows 1-3 are headings), its just the column to add the marker to (if found of course) starts at ag and progresses 1 column per s(#)

Is there an easier way to have this repeat, i.e. something like (have no idea on this - the below is just to try and illustrate)

    For i = 1 To 500

                If sTest Like s(i) Then
                    Range("b" & c.Row) = "Yes" ' i.e. just an indicator to flag a match within J / Y had been found
                    Range[B]("A" offset(32+i)[/B] & c.Row) = s(i)    'needs to be range("ag" & c.row) for s(1), range("ah" & c.row) for s(2), ai for s(3), aj for s(4) etc
                    GoTo GotOne
                End If
    Next i

Hope this makes sence?

Thanks, Upex

 

[Upex]

Think I may have got it (had to change the i to RR (as it clashed with existing i) and have renamed the s() to NRule), so the s(i) is now NRule(rr), but on a test of 3 rows, it appears to work and stick the markers (the RR value) in the right columns:

sub Highlight_N_Rules

    Dim R As Range
    Dim c As Range
    Dim i As Integer
    Dim RR As Integer
    Dim x As Integer
    Dim sColumn As String
    Dim vSplit As Variant
    Dim sTest As String
    
    Dim NRule() As String
    ReDim NRule(1 To 250)
    
    'set the NRule Refs
    For RR = 1 To 250
        NRule(RR) = Sheet14.Cells(RR + 1, 2)
    Next RR

    For i = 1 To 2
        If i = 1 Then
            sColumn = "J"
        Else
            sColumn = "Y"
        End If
        Set R = Range(Range(sColumn & 3), Range(sColumn & Sheet6.Rows.Count).End(xlUp))

        For Each c In R.Cells
            vSplit = Split(c, " ")

            For x = LBound(vSplit) To UBound(vSplit)
                sTest = Replace(vSplit(x), ",", "")
                For RR = 1 To 250
                    If sTest Like NRule(RR) Then                                         'find NRule(rr) reference within J or Y
                        Range("b" & c.Row) = "Yes"                                      'if found, add 'yes' in column B and
                        Range("ag" & c.Row).Offset(0, RR - 1) = NRule(RR)       'add whatever NRule(rr) value is, into column its respective column (starting with rr = 1 in ag, rr=2 in ah etc)
                        GoTo GotOne
                    End If
               Next RR
GotOne:
            Next x
        Next c
    Next i

End Sub

Will test across a bigger data sample, but fingers crossed this'll save me 500 different If's

Many thanks CN - and of course, if anyone can see anything to do better in the above, please let me know (the only thing that has to happen - is that it has to search the RR values in asending order - it can not happen in reverse).

Cheers CN -couldn't have done this without you.