A general programming question.

A

aah-boy

Hi all,

Just want to pick brains here for better ways to achive what I'm
doing, here goes...

I have some code which scans a worksheet for text strings, when it
finds one (using a big case statement) it puts the value of an
adjacent cell into a public variable. At the end of the scan, it
checks each variable (using a big 'if' statement) to see if there is a
value in it - if there is I prints it out.

My problem is this...

Every time the user adds a new text label (and value), I have to add
1) a new public variable 2) a new 'case' to trap it and 3) another
'if' to print it out.

Surely there must be a better way of doing this? Say by just adding
the text label to search for, the variable to put it into and the
offset cell that the value is in - to some kind of big abject. Rather
than using the 'case' and 'if' way that I am using.

Ideally it would be nice if it could add its own variables when it
finds new strings.

Any thoughts?

Thanks,
Dave
 
B

Bob Phillips

Why not put the text strings to check for in a worksheet range, load that
range in array at the start of your code, and test that array in a loop.
Also declare another array (start by declaring a dynamic array, and redim to
the size of the worksheet range). ), and load the values in that array, and
print out at then in a loop.

Here's a quick example

Dim cLastRow As Long
Dim aryStrings, aryValues()
Dim i As Long
Dim oCell As Range

With Worksheets("Sheet2")
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
aryStrings = .Range("A1:A" & cLastRow)
ReDim aryValues(cLastRow)
End With

With Worksheets("Sheet1")
For i = LBound(aryStrings, 1) To UBound(aryStrings, 1)
Set oCell = .Cells.Find(aryStrings(i, 1))
If Not oCell Is Nothing Then
aryValues(i) = oCell.Offset(0, 1).Value
End If
Next i
End With

For i = LBound(aryValues, 1) To UBound(aryValues, 1)
If Not IsEmpty(aryValues(i)) Then
Debug.Print "Item " & i & " = " & aryValues(i)
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

aah-boy

On Thu, 11 Dec 2003 22:52:11 -0000, "Bob Phillips"

Thanks Bob,

That sounds plausible - I'll do some rework later.

My next question is (and you knew this was coming - didn't you?) can I
dynamically declare public variables to take the values?

Regards,
Dave
 
B

Bob Phillips

Dave,

Not that I know of. But I have to ask, why bother, what is wrong in having a
dynamic array and loading the array? They are easy enough to work with. The
solution I proposed, the only change you need to make is add another text
string to the range on Sheet2, which I think makes it low maintenance<vbg>.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

aah-boy

Dave,
Click to expand...

Hi Bob,

Thanks for replying,
Not that I know of. But I have to ask, why bother, what is wrong in having a
dynamic array and loading the array? They are easy enough to work with. The
solution I proposed, the only change you need to make is add another text
string to the range on Sheet2, which I think makes it low maintenance<vbg>.
Click to expand...

Yes, now I think more about it, I can dump my variables altogether.

Mark up a free beer on me.

Cheers,
Dave
 
Top