Archive through November 07, 2002
MoveCloseDeleteAdmin

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

Draheid

Thursday, November 07, 2002 - 07:16 am EditMoveDeleteIP
Ok, the only 'problem' I see here is that it goes beyond the ending number if the difference isn't divisible by 20. Let me know if it works for you.

Private Sub Go_Click()
Dim intCt As Double
Dim intRCt As Double
Dim nStartNumber As Double
Dim nEndNumber As Double
Dim nTempNumber As Double
Dim counter As Double

'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

' Following to restrict from exceeding the number of available columns
If txtEndNumber - txtStartNumber > 2560 Then
MsgBox "Too many cells requested! Maximum 2560"
txtEndNumber.SetFocus
End If

'this is code to calculate the number of columns you will need
nTempNumber = nEndNumber - nStartNumber
nTempNumber = nTempNumber / 10

'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 = 1 To nTempNumber Step 2
For R = 1 To 20
CurrentNumber = CurrentNumber + 1
Cells(R, C).Value = CurrentNumber + (txtStartNumber - 1)
Cells(R, C + 1).Value = CurrentNumber + (txtStartNumber - 1)
'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


:)

Draheid

Thursday, November 07, 2002 - 08:15 am EditMoveDeleteIP
Revised again! :) Now it stops at the end of the specified range!

Private Sub Go_Click()
Dim intCt As Double
Dim intRCt As Double
Dim nStartNumber As Double
Dim nEndNumber As Double
Dim nTempNumber As Double
Dim counter As Double

'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

' Following to restrict from exceeding the number of available columns
If txtEndNumber - txtStartNumber > 2560 Then
MsgBox "Too many cells requested! Maximum 2560"
txtEndNumber.SetFocus
End If

'this is code to calculate the number of columns you will need
nTempNumber = nEndNumber - nStartNumber
nTempNumber = (nTempNumber / 10) + 1

'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 = 1 To nTempNumber Step 2
For R = 1 To 20
CurrentNumber = CurrentNumber + 1
If (CurrentNumber + (txtStartNumber - 1)) <= nEndNumber Then
Cells(R, C).Value = CurrentNumber + (txtStartNumber - 1)
Cells(R, C + 1).Value = CurrentNumber + (txtStartNumber - 1)
End If
'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

Again, hope this works for you. It appears to work here!

Zachsmom

Thursday, November 07, 2002 - 08:27 am EditMoveDeleteIP
Worked good!! I changed this bit of code

nStartNumber = CLng(txtStartNumber.Text)
nEndNumber = CLng(txtEndNumber.Text)

and changed nStartNumber & nEndNumber from integer to long..

works great guys!! I appreciate all your help!

My boss is going to think I am brillant..:)

hopefully he won't have me doing anymore excel VBA until I study up more on it..

can you recommend a good VBA book?

Thanks again!! I owe you three (Hillbilly,Twiggy & Dra dinner!!!)

Zachsmom

Thursday, November 07, 2002 - 08:30 am EditMoveDeleteIP
I changed that little bit of code that you added Dra!!

what really stinks is they are only using this for about 2 weeks..uggg!!!

Zachsmom

Thursday, November 07, 2002 - 08:37 am EditMoveDeleteIP
cleaned it up a bit..this is what the ending code comes to!!

Private Sub cmdGo_Click()


Dim nStartNumber As Long
Dim nEndNumber As Long
Dim nTempNumber As Long


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

nStartNumber = CLng(txtStartNumber.Text)
nEndNumber = CLng(txtEndNumber.Text)


'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

' Following to restrict from exceeding the number of available columns
If txtEndNumber - txtStartNumber > 2560 Then
MsgBox "Too many cells requested! Maximum 2560"
txtEndNumber.SetFocus
End If

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

nTempNumber = nEndNumber - nStartNumber
nTempNumber = (nTempNumber / 10) + 1


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


'loop code goes here to fill in the cells
Dim CurrentNumber As Long, C As Integer, R As Byte
'c=column,r=row

For C = 1 To nTempNumber Step 2
For R = 1 To 20
CurrentNumber = CurrentNumber + 1
Cells(R, C).Value = CurrentNumber + (txtStartNumber - 1)
Cells(R, C + 1).Value = CurrentNumber + (txtStartNumber - 1)
'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

frmMe.Hide


End Sub

Draheid

Thursday, November 07, 2002 - 08:38 am EditMoveDeleteIP
Well, ZM, I don't know of any books to recommend. I have never picked any up myself. I am self-taught, mostly by doing. (Ok, I will occasionally hit the F1 button) Necessity is the mother of invention and I've always managed to put together what worked for the need I had at the time. :O

Hillbilly

Thursday, November 07, 2002 - 08:47 am EditMoveDeleteIP
Good job, Draheid...yu's da man!!!

Zachsmom

Thursday, November 07, 2002 - 08:48 am EditMoveDeleteIP
I have taught myself pretty much all I know with programming too..but I do it a bit different..I always buy a book to figure out what I need to do..(okay..this gives me an excuse to buy another programming book..I always look for an excuse..:))

Draheid

Thursday, November 07, 2002 - 08:54 am EditMoveDeleteIP
I take it you don't care about stopping at the given ending number as long as it at least gets there even if it goes beyond?

Zachsmom

Thursday, November 07, 2002 - 08:57 am EditMoveDeleteIP
For now it's fine..they just go to the ending two columns and delete the cells they don't need..

Zachsmom

Thursday, November 07, 2002 - 08:58 am EditMoveDeleteIP
unless of course you have the code up your sleeve?! LOL..

Draheid

Thursday, November 07, 2002 - 09:05 am EditMoveDeleteIP
You mean this code:
If (CurrentNumber + (txtStartNumber - 1)) <= nEndNumber Then
Cells(R, C).Value = CurrentNumber + (txtStartNumber - 1)
Cells(R, C + 1).Value = CurrentNumber + (txtStartNumber - 1)
End If


:O

Zachsmom

Thursday, November 07, 2002 - 09:10 am EditMoveDeleteIP
so it should look like this?

'loop code goes here to fill in the cells
Dim CurrentNumber As Long, C As Integer, R As Byte
'c=column,r=row

For C = 1 To nTempNumber Step 2
For R = 1 To 20
CurrentNumber = CurrentNumber + 1
If (CurrentNumber + (nStartNumber - 1)) <= nEndNumber Then
Cells(R, C).Value = CurrentNumber + (nStartNumber - 1)
Cells(R, C + 1).Value = CurrentNumber + (nStartNumber - 1)
Else

Cells(R, C).Value = CurrentNumber + (nStartNumber - 1)
Cells(R, C + 1).Value = CurrentNumber + (nStartNumber - 1)
'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
End If
Next R
Next C

it still doesn't end at the nEndNumber

Draheid

Thursday, November 07, 2002 - 09:27 am EditMoveDeleteIP
Nope, only need the code I posted. If the current number is less than or equal to the ending number, it will fill the cells. If it is greater than then ending number, it skips the fill cells instructions. You don't need the Else or anything after that except the End If.

Zachsmom

Thursday, November 07, 2002 - 09:30 am EditMoveDeleteIP
you're a prince!!!!

Sia

Thursday, November 07, 2002 - 09:30 am EditMoveDeleteIP
Interjecting politely, Did you say this is used for a spreadsheet application? I used to use the Lotus 1-2-3 spreadsheet program years ago and found that very easy to manage. My current program (in Microsoft Works) doesn't seem as easy to master.

You two just amaze me!!

Draheid

Thursday, November 07, 2002 - 09:35 am EditMoveDeleteIP
Sia: This allows the user to specify a number range with any start/ending number. Then the script above fills in the sheet with double-columns of 20 rows each with the numbers between the start & ending number. I have no idea what this is for, just trying to help out! :)

Hillbilly

Thursday, November 07, 2002 - 09:40 am EditMoveDeleteIP
Zmom...even though they may say they only need this for two weeks. I would put this in my arsenal of 'utilities'. These things have a way of spreading by word of mouth....'hey, someone over somewhere has a program that might do what we're looking for.' They'll be calling!

Zachsmom

Thursday, November 07, 2002 - 09:41 am EditMoveDeleteIP
I have no idea what this is for, just trying to help out!

This was used to test my sanity..with a little help from Draheid,Hillbilly & Twiggy..my boss believes I am brillant and absolutely sane..

Sia: never used microsoft works..but I think it's a generic version of microsoft office..I have never used Lotus but I did learn Quattro many many years ago..spreadsheet apps have come a long way since then!!!!

Zachsmom

Thursday, November 07, 2002 - 09:43 am EditMoveDeleteIP
Hillbilly..as soon as I either create or find code that works..I have a special file that I put it in!! You absolutely never know when someone else might need it..or when you need it again..I create things and then don't remember how I do them..lol..all with the learning process..:)

Draheid

Thursday, November 07, 2002 - 09:47 am EditMoveDeleteIP
ZM: *I* learned on VisiCalc! Then moved up to Multiplan! LOL, talk about an antique programs!

Microsoft Works is similar to most 'home suites' in that all of the applications are there, but extremely limited in their capabilities. With the exception of Microsoft Works Suite 2002 (which actually includes the FULL version of MS Word 2002), I have found these types of suites to be barely useful even in for simple home computer projects. There's nothing really wrong with them, it's just that they are intended to be very easy to use.

Just my FWIW

Hillbilly

Thursday, November 07, 2002 - 09:49 am EditMoveDeleteIP
I predict you're fast becoming the company 'excel vba expert.' I think its time to ask for that raise!

Zachsmom

Thursday, November 07, 2002 - 10:00 am EditMoveDeleteIP
I'd feel compelled to share the earnings with you guys Hillbilly..:)

What I find amazing is I create many database applications and other sophisticated programs, but as soon as my boss says "I need this to be in Excell and I want it to do such and such" I freeze like a deer caught in headlights and feel like a complete idiot!

Looks like I need to invest in some books!!! (Not that I mind..heehee)

Hillbilly

Thursday, November 07, 2002 - 10:12 am EditMoveDeleteIP
No....don't feel that way, Zmom. Sharing code is the way it works in the computer industry. Why reinvent the wheel if its already been invented. I constantly 'borrow' code and makefiles from fellow programmers here. It's encouraged. Its a great timesaver and much more productive for the company. We all share with each other and its a great way to learn by example. I love looking at other people's coding style to see if they have any techniques I might want to use.

It is overwhelming when you look at all the different programming languages available. For me, it takes a few days to switch from a C/C++ unix server mindset to a Visual Basic intel mindset.

The newsgroups on google are excellent. Sometimes you might have to be a little persistent to get a response but someone will usually help you out. I used them alot on an XML parsing project I had to do a few months back.

Draheid

Thursday, November 07, 2002 - 10:21 am EditMoveDeleteIP
Zachsmom, might I suggest you consider purchasing books/software on Visual Basic (not specific to VBA) since it's a stand-alone development package, you could use it for things outside the applications area but the information is still viable when working in VBA. (Does that make sense? lol) There appears to be only one version of Visual Basic (.NET) which has a limited platform compatibility, but you could look around and maybe pick up a 'Learning Edition' for cheap. Then you could create your own software with that while learning about VB! :)