By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

vb for excel

P: 63
Hi. I am trying build an array in vb applications for excel. I am posting the code for what I am trying to write. Any help would be appreciated greatly..

Expand|Select|Wrap|Line Numbers
  1. Sheets("C16-M-001").Select
  2.    Range("A2:H2").Select
  3.    If Range("C2").Value < (Date + 6) And Range("E2").Value < 10 Then
  4.         Selection.Copy
  5.         Sheets("SCHEDULE").Select
  6.         Range("A8:H8").Select
  7.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  8.             :=False, Transpose:=False
  9.      ElseIf Range("C3").Value < (Date + 6) And Range("E3").Value < 100 Then
  10.         Range("A3:H3").Select
  11.         Selection.Copy
  12.         Sheets("SCHEDULE").Select
  13.         Range("A8:H8").Select
  14.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  15.             :=False, Transpose:=False
  16.  
  17.     ElseIf Range("C4").Value < (Date + 6) And Range("E4").Value < 100 Then
  18.         Range("A4:H4").Select
  19.         Selection.Copy
  20.         Sheets("SCHEDULE").Select
  21.         Range("A8:H8").Select
  22.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  23.             :=False, Transpose:=False
  24.     ElseIf Range("C5").Value < (Date + 6) And Range("E5").Value < 100 Then
  25.         Range("A5:H5").Select
  26.         Selection.Copy
  27.         Sheets("SCHEDULE").Select
  28.         Range("A8:H8").Select
  29.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  30.             :=False, Transpose:=False
  31.  
  32.     ElseIf Range("C6").Value < (Date + 6) And Range("E6").Value < 100 Then
  33.         Range("A6:H6").Select
  34.         Selection.Copy
  35.         Sheets("SCHEDULE").Select
  36.         Range("A8:H8").Select
  37.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  38.             :=False, Transpose:=False
  39.  
  40.          ElseIf Range("C7").Value < (Date + 6) And Range("E7").Value < 100 Then
  41.         Range("A7:H7").Select
  42.         Selection.Copy
  43.         Sheets("SCHEDULE").Select
  44.         Range("A8:H8").Select
  45.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  46.             :=False, Transpose:=False
  47.  
  48.     ElseIf Range("C8").Value < (Date + 6) And Range("E8").Value < 100 Then
  49.         Range("A8:H8").Select
  50.         Selection.Copy
  51.         Sheets("SCHEDULE").Select
  52.         Range("A8:H8").Select
  53.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  54.             :=False, Transpose:=False
  55.     ElseIf Range("C9").Value < (Date + 6) And Range("E9").Value < 100 Then
  56.         Range("A9:H9").Select
  57.         Selection.Copy
  58.         Sheets("SCHEDULE").Select
  59.         Range("A8:H8").Select
  60.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  61.             :=False, Transpose:=False
  62.  
  63.     ElseIf Range("C10").Value < (Date + 6) And Range("E10").Value < 100 Then
  64.         Range("A10:H10").Select
  65.         Selection.Copy
  66.         Sheets("SCHEDULE").Select
  67.         Range("A8:H8").Select
  68.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  69.             :=False, Transpose:=False
  70.  
  71.     ElseIf Range("C11").Value < (Date + 6) And Range("E11").Value < 100 Then
  72.         Range("A11:H11").Select
  73.         Selection.Copy
  74.         Sheets("SCHEDULE").Select
  75.         Range("A8:H8").Select
  76.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  77.             :=False, Transpose:=False
  78.  
  79.     ElseIf Range("C12").Value < (Date + 6) And Range("E12").Value < 100 Then
  80.         Range("A12:H12").Select
  81.         Selection.Copy
  82.         Sheets("SCHEDULE").Select
  83.         Range("A8:H8").Select
  84.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  85.             :=False, Transpose:=False
  86.     ElseIf Range("C13").Value < (Date + 6) And Range("E13").Value < 100 Then
  87.         Range("A13:H13").Select
  88.         Selection.Copy
  89.         Sheets("SCHEDULE").Select
  90.         Range("A8:H8").Select
  91.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  92.             :=False, Transpose:=False
May 2 '07 #1
Share this Question
Share on Google+
4 Replies


SammyB
Expert 100+
P: 807
Hi. I am trying build an array in vb applications for excel. I am posting the code for what I am trying to write. Any help would be appreciated greatly..
If you have the code, why do you need help?

Seriously, you said "I am trying to build an array," but I do not see any array. Also, you post code that we cannot run. What is the problem (what is failing) and what are you trying to do? Thanks! --Sam
May 2 '07 #2

P: 63
I posted the existing code sent to me.The person was getting the "Procedure to large". I could not post sll of the module here as the size limit won't allow it.
May 2 '07 #3

SammyB
Expert 100+
P: 807
I posted the existing code sent to me.The person was getting the "Procedure to large". I could not post sll of the module here as the size limit won't allow it.
Ah, Excel is evil that way. You think that everything works great and add one thing, and you get screwy error messages. What has worked for me is to start over. :D

It's not that bad. You want to export all of the code modules, classes, and forms. You also want to copy any workbook/worksheet code and paste it into text files. Then delete all of the code that you have exported. Do a SaveAs with your workbook, close it and reopen. When you don't get any enable macros prompts, you know that you have removed all of the code.

Next, use the Edit, Move or copy sheets menu to create a new workbook from scratch. Again, close down Excel and try to open the new workbook. If it asks if you want to update links, you have a big problem: you'll need to recopy or correct the formulas so as to remove the links.

Now, you can import the code modules, classes, and forms back into you new workbook. Finally, open the text files and copy/paste the code into the proper worksheet/workbook.

Finally, you really, really need to write better code. All of those Selects are killing you. You very rarely need to do a Select. For example, your first 8 lines should look something like:
Expand|Select|Wrap|Line Numbers
  1. With Sheets("C16-M-001")
  2.     If .Range("C2").Value < (Date + 6) And .Range("E2").Value < 10 Then
  3.         .Range("A2:H2").Copy
  4.         Sheets("SCHEDULE").Range("A8:H8").PasteSpecial Paste:=xlPasteValues, _
  5.             Operation:=xlNone, SkipBlanks:=False, Transpose:=False
or even
Expand|Select|Wrap|Line Numbers
  1.     With Sheets("C16-M-001")
  2.         If .Range("C2").Value < (Date + 6) And .Range("E2").Value < 10 Then _
  3.             Sheets("SCHEDULE").Range("A8:H8").Value = .Range("A2:H2").Value
  4.     End With
Also at the top of each module/class/form you should have Option Explicit which will make sure that you have properly defined all of your variables. Hope this helps a little. Depending on the amount of code, you have a large job to do.
May 2 '07 #4

P: 63
Thanks SammyB. The person doing the project is getting ready to rework it, but isn't a real exp programmer. It was originally written by her supervisor who simply recorded macros then pasted the code into modules, then copy and pastes over and over and over, you get the point. I will pass on the info to the person in nedd, and thanks again for the tips!
May 3 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.