Archive through November 07, 2002
TV ClubHouse: Archives: HELP!! Anyone know VBA?? Have Excel Question!!!:
Archive through November 07, 2002
Draheid | Thursday, November 07, 2002 - 07:16 am     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     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     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     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     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     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. |
Hillbilly | Thursday, November 07, 2002 - 08:47 am     Good job, Draheid...yu's da man!!! |
Zachsmom | Thursday, November 07, 2002 - 08:48 am     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     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     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     unless of course you have the code up your sleeve?! LOL.. |
Draheid | Thursday, November 07, 2002 - 09:05 am     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
 |
Zachsmom | Thursday, November 07, 2002 - 09:10 am     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     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     you're a prince!!!! |
Sia | Thursday, November 07, 2002 - 09:30 am     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     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     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     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     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     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     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     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     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     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!  |
|