Archive through November 07, 2002
MoveCloseDeleteAdmin

TV ClubHouse: Archives: HELP!! Anyone know VBA?? Have Excel Question!!!: Archive through November 07, 2002

Reader234

Friday, September 20, 2002 - 04:21 pm EditMoveDeleteIP
(aw come on now, ya mean this thread had NOTHING to do with Vaginal Birth After... )

(I is in one straaaaaaaaaaange mood...)

Hillbilly

Wednesday, November 06, 2002 - 06:09 pm EditMoveDeleteIP
bump...you here zmom?

Zachsmom

Wednesday, November 06, 2002 - 06:14 pm EditMoveDeleteIP
Okay..have this excel spreadsheet with a user form. the form has two text boxes.

txtStartNumber
txtEndNumber

and a command button.

the user enters a start number..for this example we will start at 1..but the start number could be something like 33453..but for simplicity let's just do 1

the end number needs to be higher than the start number

for this example we will say 500

so we have txtStartNumber = 1
txtEndNumber = 500

the user presses go

I need this to create a new workbook and the values to be set as follows

A
1
2
3
4
5
6
7
8
til we reach row 20

column B will be the same as column A

I then need for column C to pick up with the number that is in column B20 and add 1

so column C1 would be 21.. this needs to go down 20 rows also..

column D would be exactly the same as Column C

I need this to go on until the ending number is reached..

I have some code but It's incorrect..

Private Sub cmdGo_Click()
Dim intCt As Integer
Dim intRCt As Integer
For intCt = txtStartNumber To txtEndNumber
For intRCt = 1 To 20
Range("A" & intCt + intRCt - 1).Value = intRCt
Range("B" & intCt + intRCt - 1).Value = intRCt
Range("C" & intCt + intRCt - 1).Value = intRCt + 20
Range("D" & intCt + intRCt - 1).Value = intRCt + 20
Range("E" & intCt + intRCt - 1).Value = intRCt + 40
Range("F" & intCt + intRCt - 1).Value = intRCt + 40
Range("G" & intCt + intRCt - 1).Value = intRCt + 60
Range("H" & intCt + intRCt - 1).Value = intRCt + 60
Range("I" & intCt + intRCt - 1).Value = intRCt + 80
Range("J" & intCt + intRCt - 1).Value = intRCt + 80
Range("K" & intCt + intRCt - 1).Value = intRCt + 100
Range("L" & intCt + intRCt - 1).Value = intRCt + 100
Range("M" & intCt + intRCt - 1).Value = intRCt + 120
Range("N" & intCt + intRCt - 1).Value = intRCt + 120
Range("O" & intCt + intRCt - 1).Value = intRCt + 140
Range("P" & intCt + intRCt - 1).Value = intRCt + 140
Range("Q" & intCt + intRCt - 1).Value = intRCt + 160
Range("R" & intCt + intRCt - 1).Value = intRCt + 160
Range("S" & intCt + intRCt - 1).Value = intRCt + 180
Range("T" & intCt + intRCt - 1).Value = intRCt + 180
Range("U" & intCt + intRCt - 1).Value = intRCt + 200
Range("V" & intCt + intRCt - 1).Value = intRCt + 200
Range("W" & intCt + intRCt - 1).Value = intRCt + 220
Range("X" & intCt + intRCt - 1).Value = intRCt + 220
Range("Y" & intCt + intRCt - 1).Value = intRCt + 240
Range("Z" & intCt + intRCt - 1).Value = intRCt + 240
Next intRCt
intCt = intCt + intRCt - 2
Next intCt


thanks for you help..

why my boss has me do excel VBA is beyond me..he knows I don't know ranges very well (or at all I should say!!!)

Zachsmom

Wednesday, November 06, 2002 - 06:15 pm EditMoveDeleteIP
the above code makes a complete mess!!!!!!!

Hillbilly

Wednesday, November 06, 2002 - 06:17 pm EditMoveDeleteIP
when do you need this by?

Twiggyish

Wednesday, November 06, 2002 - 06:57 pm EditMoveDeleteIP
I'll also take a peek at it.

Twiggyish

Wednesday, November 06, 2002 - 07:16 pm EditMoveDeleteIP
So far, I'm getting a range error. Still working on it.

Zachsmom

Wednesday, November 06, 2002 - 07:16 pm EditMoveDeleteIP
Thanks Twiggy!!!

Twiggyish

Wednesday, November 06, 2002 - 07:21 pm EditMoveDeleteIP
Zach, change the negative 1 to a positive 1 all the way down.

I can paste my code in here.

Twiggyish

Wednesday, November 06, 2002 - 07:22 pm EditMoveDeleteIP
Private Sub cmdGo_Click()
Dim intCt As Integer
Dim intRCt As Integer
For intCt = txtStartNumber To txtEndNumber
For intRCt = 1 To 20
Range("A" & intCt + intRCt + 1).Value = intRCt
Range("B" & intCt + intRCt + 1).Value = intRCt
Range("C" & intCt + intRCt + 1).Value = intRCt + 20
Range("D" & intCt + intRCt + 1).Value = intRCt + 20
Range("E" & intCt + intRCt + 1).Value = intRCt + 40
Range("F" & intCt + intRCt + 1).Value = intRCt + 40
Range("G" & intCt + intRCt + 1).Value = intRCt + 60
Range("H" & intCt + intRCt + 1).Value = intRCt + 60
Range("I" & intCt + intRCt + 1).Value = intRCt + 80
Range("J" & intCt + intRCt + 1).Value = intRCt + 80
Range("K" & intCt + intRCt + 1).Value = intRCt + 100
Range("L" & intCt + intRCt + 1).Value = intRCt + 100
Range("M" & intCt + intRCt + 1).Value = intRCt + 120
Range("N" & intCt + intRCt + 1).Value = intRCt + 120
Range("O" & intCt + intRCt + 1).Value = intRCt + 140
Range("P" & intCt + intRCt + 1).Value = intRCt + 140
Range("Q" & intCt + intRCt + 1).Value = intRCt + 160
Range("R" & intCt + intRCt + 1).Value = intRCt + 160
Range("S" & intCt + intRCt + 1).Value = intRCt + 180
Range("T" & intCt + intRCt + 1).Value = intRCt + 180
Range("U" & intCt + intRCt + 1).Value = intRCt + 200
Range("V" & intCt + intRCt + 1).Value = intRCt + 200
Range("W" & intCt + intRCt + 1).Value = intRCt + 220
Range("X" & intCt + intRCt + 1).Value = intRCt + 220
Range("Y" & intCt + intRCt + 1).Value = intRCt + 240
Range("Z" & intCt + intRCt + 1).Value = intRCt + 240
Next intRCt
intCt = intCt + intRCt - 2
Next intCt

End Sub
'
' Macro1 Macro
' Macro recorded 11/6/2002 by me

'
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/6/2002 by me

'
End Sub

Twiggyish

Wednesday, November 06, 2002 - 07:23 pm EditMoveDeleteIP
I hope this is what you needed.

Hillbilly

Wednesday, November 06, 2002 - 07:42 pm EditMoveDeleteIP
Private sub Goclick()

Dim intCt As Integer
Dim intRCt As Integer
Dim nStartNumber As Integer
Dim nEndNumber As Integer
Dim nTempNumber As Integer

!this is hardcoded values to use for debugging
!these will actually come from your user form

nStartNumber = 1
nEndNumber = 500
intRCt = 1

!this is code to check that the end number is greater
!than the starting number
!i usually give an error message and code to
!exit gracefully

If nEndNumber < nStartNumber Then
MsgBox "end number must be greater than start number"
do something to exit gracefully
End If

!this is code to calculate the number of columns you will need

nTempNumber = nEndNumber - nStartNumber
nTempNumber = nTempNumber / 20

!this is code to activate the worksheet
Worksheets("Sheet1").Activate

!i would recommend referencing the cells by index
!example: range(6,1) is cell A6


!loop code goes here to fill in the cells

End Sub

Twiggyish

Wednesday, November 06, 2002 - 07:44 pm EditMoveDeleteIP
I got it to run with the positive one. I'll have to try yours, too Hillbilly.

Zachsmom

Wednesday, November 06, 2002 - 07:49 pm EditMoveDeleteIP
Thanks HB & Twiggy!! Again- you come to my rescue!!!

Hillbilly

Wednesday, November 06, 2002 - 07:50 pm EditMoveDeleteIP
For counter = 1 To 20
Worksheets("Sheet1").Cells(counter, nTempNumber).Value = counter
Next counter

Hillbilly

Wednesday, November 06, 2002 - 07:56 pm EditMoveDeleteIP
zmom...the last example i posted is the correct index referencing...not the 'range' one

Zachsmom

Wednesday, November 06, 2002 - 08:01 pm EditMoveDeleteIP
Okay..going to start working on this..I wish I could express into words my thanks!

Juju2bigdog

Wednesday, November 06, 2002 - 08:32 pm EditMoveDeleteIP
<spins head backwards>

Sia

Wednesday, November 06, 2002 - 11:13 pm EditMoveDeleteIP
Geez, Juju, my head is spinning, too! Man, did I ever stray into the wrong thread! Yikes, this is frightening. Scarier yet, they seem to understand one another!

Hillbilly

Thursday, November 07, 2002 - 04:12 am EditMoveDeleteIP
Be afwaid, Sia...be vewwy afwaid!

<hillbilly laughs wickedly>

Zachsmom

Thursday, November 07, 2002 - 05:43 am EditMoveDeleteIP
Hillbilly..one of the guys on a messageboard gave me this..can this be intergrated with the above code? Haven't had coffee yet..so I don't know if I am reading it right..


dim currentnumber as long,C as integer,R as byte
'c=column,r=row

For c= txtStartNumber To txtEndNumber step 2
for r=1 to 20
currentnumber=currentnumber+1
cells(r,c).value =currentnumber
cells(r,c+1).value =currentnumber
'if you had more then 2 columns the same I would put a counter here as well
'but since only 2 have to be the same this will do
next r
next c

Zachsmom

Thursday, November 07, 2002 - 05:54 am EditMoveDeleteIP
This works on the double columns, but it doesn't end with the end number..so close..yet so far..<sigh>

Draheid

Thursday, November 07, 2002 - 06:05 am EditMoveDeleteIP
Do you still not have this working? I was going to hack at it last night but it looked like you had enough people working on it that I didn't want to confuse the issue. If you still need help with this one, let me know. I've got plenty of time on my hands and would be happy to at least give it a shot.

Zachsmom

Thursday, November 07, 2002 - 06:16 am EditMoveDeleteIP
This works as long as the start number is 1. But as soon as I put 334455 as start number and 335560 as end number I recieve a runtime error "6" of overflow!!!


Dra- here's the code that I have so far!

Private Sub cmdClear_Click()
Range("A1:IL20").Select
Selection.ClearContents
Range("A1").Select
frmMe.txtStartNumber.Text = ""
frmMe.txtEndNumber.Text = ""
End Sub

Private Sub cmdGo_Click()
Dim intCt As Integer
Dim intRCt As Integer
Dim nStartNumber As Integer
Dim nEndNumber As Integer
Dim nTempNumber As Integer
Dim counter As Integer

'this is hardcoded values to use for debugging
'these will actually come from your user form

nStartNumber = txtStartNumber.Text
nEndNumber = txtEndNumber.Text
intRCt = 1

'this is code to check that the end number is greater
'than the starting number
'i usually give an error message and code to
'exit gracefully

If nEndNumber < nStartNumber Then
MsgBox "end number must be greater than start number"
'do something to exit gracefully
txtEndNumber.SetFocus
End If

'this is code to calculate the number of columns you will need

nTempNumber = nEndNumber - nStartNumber
nTempNumber = nTempNumber / 20

'this is code to activate the worksheet
Worksheets("Sheet1").Activate


'loop code goes here to fill in the cells
nEndNumber = nEndNumber / 10

Dim CurrentNumber As Long, C As Integer, R As Byte
'c=column,r=row

For C = nStartNumber To nEndNumber Step 2
For R = 1 To 20
CurrentNumber = CurrentNumber + 1
Cells(R, C).Value = CurrentNumber
Cells(R, C + 1).Value = CurrentNumber
'if you had more then 2 columns the same I would put a counter here as well
'but since only 2 have to be the same this will do
Next R
Next C

End Sub


If you want I can email you the spreadsheet with the user form..

Draheid

Thursday, November 07, 2002 - 07:06 am EditMoveDeleteIP
Haven't forgotten. I've almost got it figured out. :)
(I think!)