VBA Setting a variable to a number

[dprayner]

Hi people. I am trying to create a Public variable (num) and set the value to 6. I want to use this to reference a cell in a specific range (ie. Range("A" & num) = Range ("A6"). I was able to declare the Public variable in the module, but I am have trouble with setting the variable to 6. If I use Set num = 6 it comes up with an error, or if I put num = 6 the value of num is empty. I have done this in the past with a For Next, but I don't require it for this particular purpose. Any suggestions? DAVE

 

[Zathras]

"Set" is used for objects, hence the error.

num = 6 should work. Perhaps if you post some of your code we can see what is wrong.

For what you have described, a constant declaration would be just as good and possibly provide better documentation.

 

[dprayner]

Hi people. Here is the code:

Private Sub UserForm_Initialize()
row = 6
txtCheckAmt.Text = Range("B6").Text
txtLockbox.Text = Range("C6").Text
txtPolicy.Text = Range("D6").Text
txtSubtotal.Text = Range("F2").Text
txtItems.Text = Range("F1").Text
End Sub
Private Sub cmdBack_Click()
If row > 10 Then
row = row - 1
txtCheckAmt.Text = Range("B" & row)
txtLockbox.Text = Range("C" & row)
txtPolicy.Text = Range("D" & row)
txtSubtotal.Text = Range("F2")
txtItems.Text = Range("F1")
End If
End Sub
Private Sub cmdExit_Click()
Unload UserForm1
End Sub
Private Sub cmdNext_Click()
If txtCheckAmt.Text = "" And txtLockbox.Text = "" And txtPolicy.Text = "" Then
Range("B" & row).Text = txtCheckAmt.Text
Range("C" & row).Text = txtLockbox.Text
Range("D" & row).Text = txtPolicy.Text
End If
txtSubtotal.Text = Range("F2")
txtItems.Text = Range("F1")
row = row + 1
End Sub

Module1:

Public row As Integer

I hope it helps. This should be a simple matter. I miss Basic. DAVE

 

[SkipVought]

...and your question is??????????

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[dprayner]

Hi Skip. The previous responder wanted me to post some code. As I stated in theabove posts, I am trying to assign a value to a variable. In this case row = 6. DAVE

 

[PHV]

Public row As Long
Private Sub UserForm_Initialize()
row = 6
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

which you do in the initialize procedure.

However, have you declared row as a module variable, since you are using it in more than one procedure?

Check out help on the Scope of variables.



Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Zathras]

You can avoid gross blunders like this if you simply put
[tt]
Option Explicit
[/tt]
at the top of each module. Best bet: tick "Require Variable Declaration" in the Editor tab of the Options dialog. That will do it for you automatically.

 

[AnotherHiggins]

I've said it before and I'll say it again: I'm no programmer.

But is it wise to use row as a variable? Row is already used by VBA. Wouldn't it be better to use something like myRow to avoid confusion?

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Zathras]

Good point, John.

dprayner, I see you have declared "row" as a global variable in Module1. (I missed that, first time around.) So, it should be working. I can't quite see the problem yet. (Option Explicit should still be used, but won't solve this problem.)

I'll keep working on it unless someone else comes up with an answer first.

 

[dprayner]

Thanks everyone. I have it working. I used the Option Explicit and deleted the module. I have changed the row to rownum. DAVE