Reader234 | Friday, September 20, 2002 - 04:21 pm     (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     bump...you here zmom? |
Zachsmom | Wednesday, November 06, 2002 - 06:14 pm     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     the above code makes a complete mess!!!!!!! |
Hillbilly | Wednesday, November 06, 2002 - 06:17 pm     when do you need this by? |
Twiggyish | Wednesday, November 06, 2002 - 06:57 pm     I'll also take a peek at it. |
Twiggyish | Wednesday, November 06, 2002 - 07:16 pm     So far, I'm getting a range error. Still working on it. |
Zachsmom | Wednesday, November 06, 2002 - 07:16 pm     Thanks Twiggy!!! |
Twiggyish | Wednesday, November 06, 2002 - 07:21 pm     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     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     I hope this is what you needed. |
Hillbilly | Wednesday, November 06, 2002 - 07:42 pm     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     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     Thanks HB & Twiggy!! Again- you come to my rescue!!! |
Hillbilly | Wednesday, November 06, 2002 - 07:50 pm     For counter = 1 To 20 Worksheets("Sheet1").Cells(counter, nTempNumber).Value = counter Next counter |
Hillbilly | Wednesday, November 06, 2002 - 07:56 pm     zmom...the last example i posted is the correct index referencing...not the 'range' one |
Zachsmom | Wednesday, November 06, 2002 - 08:01 pm     Okay..going to start working on this..I wish I could express into words my thanks! |
Juju2bigdog | Wednesday, November 06, 2002 - 08:32 pm     <spins head backwards>
 |
Sia | Wednesday, November 06, 2002 - 11:13 pm     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     Be afwaid, Sia...be vewwy afwaid! <hillbilly laughs wickedly> |
Zachsmom | Thursday, November 07, 2002 - 05:43 am     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     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     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     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     Haven't forgotten. I've almost got it figured out. (I think!) |