Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA Coding Error Question

  Asked By: Gail    Date: Dec 19    Category: MS Office    Views: 690
  

I want to copy and past the VBA code that is returning an error message
and I cannot figure out what is wrong with my program. Can one of your
kind assistants please help me with this? Thank you so much.


(ps - here is the code)



Sub OBTest()

'

' OBTest Macro

' Macro recorded 8/25/2008

'

' Keyboard Shortcut: Ctrl+Shift+O

'

Dim row As Integer

For row = 1 To 30000

row = 1

Do Until ActiveSheet.Range("E(row + 1)") Is Empty

row = row + 1

Sheets("Sheet1").Select

Range("E(row + 1)").Select

Selection.Copy

Sheets("Calculation Template").Select

Range("D4").Select

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Sheet1").Select

Range("F(row + 1)").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Calculation Template").Select

Range("D5").Select

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Sheet1").Select

Range("G(row + 1)").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Calculation Template").Select

Range("D6").Select

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Sheet1").Select

Range("H(row + 1)").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Calculation Template").Select

Range("D7").Select

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Sheet1").Select

Range("I(row + 1)").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Calculation Template").Select

Range("D8").Select

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Sheet1").Select

Range("J(row + 1)").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Calculation Template").Select

Range("D11").Select

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Sheet1").Select

Range("K(row + 1)").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Calculation Template").Select

Range("D8").Select

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Calculation Template").Select

Range("D39").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Sheet1").Select

Range("M2").Select

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Loop



Next row



End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Kristin Johnston     Answered On: Dec 19

Normally, I would say that it would help  if we knew
what the error  was and where it occurs, but in this case,
I can see right off that you've got several problems!
You say:
for row  = 1 to 30000
then you immediately say:
row = 1
that means that in each loop (of 30000)
"row" will ALWAYS be 1 !!! because you TOLD it TO!
also, I would suggest NOT using variables that are the
same as reserved words (or methods, or procedures)
because it can cause problems.
like if you're trying to determine the current row
and say: ActiveCell.row
is that Excel's ROW, or YOUR ROW (which would cause an error).
There's also lots of confusing things here.
The For row = 1 to 300000 will keep incrementing "row" by 1
but then you have row = row + 1, which ALSO increments "row".
Also.. the statement: Range("E(row + 1)").Select
is NOT legal. there is no range called "E(row + 1)"
because the quotes (") indicate that you want to use this
LITERALLY, not use the values they represent.
so... I think we need a "melt and re-pour" here...
tell us what you want to do...
the looping part is much easier than you're making it.
for I = 1 to 30000
if (cells(I,5) = "") then exit for
Next I

 
Answer #2    Answered By: Beatriz Silva     Answered On: Dec 19

What I'm trying to do is write a Sub that will function as a generic
program and run several records through my calculator template. The
calculated premium should be returned back to column M in Sheet1.
Obviously, your comments mean I've greatly messed this up. Is it easier
to call me and we can walk through this program  on the phone? Thanks
again for your help.

 
Didn't find what you were looking for? Find more on VBA Coding Error Question Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post