PDA

View Full Version : Solved: One variable defined as text, the other as integer Problem



Shortz
04-06-2010, 03:45 AM
Hi,
i've got a problem with the following code.

Sub PPFilter()
Dim ListofNames As Range
Dim a, b As Integer
Dim PPL, PPU As integer

'Target = Workgroup selection
Outsheet = SQForm.OSName.Value
PPL = SQForm.PPLower.Value
PPU = SQForm.PPUpper.Value

'Highlights the name list from the "output worksheet
Sheets(Outsheet).Activate
Set ListofNames = Sheets(Outsheet).Range(Cells(2, 5), Cells(2, 5).End(xlToRight))
a = Cells(2, 5).End(xlToRight).Column

ListofNames.Select
Do Until ActiveCell.Value = ""
Select Case (ActiveCell.Offset(2, 0).Value <= PPU And ActiveCell.Offset(2, 0).Value >= PPL)
Case False
ActiveCell.EntireColumn.Delete
Case True
ActiveCell.Offset(0, 1).Activate
End Select
Loop

End Sub
The PPL automatically defines itself as "" while the PPU automatically defines itself as 0
This macro is part of a larger set of macros and i was wondering why it would do this?

I've looked through the rest of my code and cannot find anything that would indicate this from the beginning.

The variables in question take their values from a text box on a userform.

Any help would be really appreciated as this is killing me! and it royally stuffs up the code.

thanks guys!

Shortz
04-06-2010, 03:50 AM
Ok, while playing around with the code, i made PPU and PPL each seperate statements, e.g.

Dim PPU as integer
Dim PPL as integer

'rather than
Dim PPU, PPL as integer
Could someone please tell me why this works? does adding the comma in the above code make the PPL a function or sub variable of the PPU??

Shortz
04-06-2010, 03:59 AM
Nevermind, i think i get it now. Major retard! Please delete this post

GTO
04-06-2010, 04:01 AM
The PPL automatically defines itself as "" while the PPU automatically defines itself as 0

This macro is part of a larger set of macros and i was wondering why it would do this?

Greetings,

Reference:

Dim a, b As Integer
Dim PPL, PPU As Integer

You were declaring both 'a' and 'PPL' as Variants. Each variable must have the type specified after the variable name and the As keyword.

Taking PPL for instance, as you had inadvertantly declared it as a Variant, as you stepped thru the code (I am presuming that neither textbox had a value in them; thus PPU stayed at a zero value), PPL's value changed from Empty to vbNullString (""), as the value from a textbox is a String ( ie - Text), and thus an empty string if the box is empty.

Does that make sense?

Mark

mdmackillop
04-06-2010, 05:27 AM
Use Long rather than Integer, as the Integer values are converted to Long (http://msdn.microsoft.com/en-us/library/aa164754(office.10).aspx)regardless. Also Integer cannot store values greater than 32,767, so Row numbers etc. can easily exceed the Integer limit