A general programming question.

  • Thread starter Thread starter aah-boy
  • Start date Start date
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
 
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)
 
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
 
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)
 
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
 
Back
Top