HELP!!

G

Guest

I keep getting a compile error on the formula below and I can't figure out
what I have wrong... The error comes up 'duplicate declaration in current
scope' right under 'Creates Formula for LO Commission....Can you guys help me?

'Creates formula for Total Fee Income
Dim cell As Range
For Each cell In Range("L1:L100")
cell.Replace What:="0", Replacement:="=(E" & cell.Row & "+F" & cell.Row & _
"+G" & cell.Row & "+H" & cell.Row & ")-K" & cell.Row, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

'Creates Formula for LO Commission
Dim cell As Range
For Each cell In Range("N1:N100")
cell.Replace What:="0", Replacement:="=(L" & cell.Row & "*M" & cell.Row & _
")-J" & cell.Row, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

Next
Range("J5").Select

'Adds $ formatting
Columns("D:D").Select
ActiveWindow.SmallScroll ToRight:=3
Columns("D:L").Select
Selection.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*
""-""??_);_(@_)"
Columns("N").Select
Selection.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*
""-""??_);_(@_)"
Range("A1").Select
End Sub
 
D

Don Guillett

Help is not a good subject line for the archives. Try commenting out the 2nd
dim cell as range.
'Adds $ formatting

also try this
range("d1:l1,n1").entireColumn.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*

End Sub
 
G

Guest

I get the error for the LO commission calculation. Not the formatting at the
bottom.

Thanks.
--
Thanks a bunch!
rojobrown


Don Guillett said:
Help is not a good subject line for the archives. Try commenting out the 2nd
dim cell as range.
'Adds $ formatting

also try this
range("d1:l1,n1").entireColumn.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*

End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
rojobrown said:
I keep getting a compile error on the formula below and I can't figure out
what I have wrong... The error comes up 'duplicate declaration in current
scope' right under 'Creates Formula for LO Commission....Can you guys help
me?

'Creates formula for Total Fee Income
Dim cell As Range
For Each cell In Range("L1:L100")
cell.Replace What:="0", Replacement:="=(E" & cell.Row & "+F" & cell.Row &
_
"+G" & cell.Row & "+H" & cell.Row & ")-K" & cell.Row, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

'Creates Formula for LO Commission
Dim cell As Range
For Each cell In Range("N1:N100")
cell.Replace What:="0", Replacement:="=(L" & cell.Row & "*M" & cell.Row &
_
")-J" & cell.Row, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

Next
Range("J5").Select

'Adds $ formatting
Columns("D:D").Select
ActiveWindow.SmallScroll ToRight:=3
Columns("D:L").Select
Selection.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*
""-""??_);_(@_)"
Columns("N").Select
Selection.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*
""-""??_);_(@_)"
Range("A1").Select
End Sub
Click to expand...
Click to expand...
 
G

Guest

I tried commenting out the 2nd dim cell as range. It still didn't work.
I'll try the one for the formatting too. Thanks.
--
Thanks a bunch!
rojobrown


Don Guillett said:
Help is not a good subject line for the archives. Try commenting out the 2nd
dim cell as range.
'Adds $ formatting

also try this
range("d1:l1,n1").entireColumn.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*

End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
rojobrown said:
I keep getting a compile error on the formula below and I can't figure out
what I have wrong... The error comes up 'duplicate declaration in current
scope' right under 'Creates Formula for LO Commission....Can you guys help
me?

'Creates formula for Total Fee Income
Dim cell As Range
For Each cell In Range("L1:L100")
cell.Replace What:="0", Replacement:="=(E" & cell.Row & "+F" & cell.Row &
_
"+G" & cell.Row & "+H" & cell.Row & ")-K" & cell.Row, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

'Creates Formula for LO Commission
Dim cell As Range
For Each cell In Range("N1:N100")
cell.Replace What:="0", Replacement:="=(L" & cell.Row & "*M" & cell.Row &
_
")-J" & cell.Row, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

Next
Range("J5").Select

'Adds $ formatting
Columns("D:D").Select
ActiveWindow.SmallScroll ToRight:=3
Columns("D:L").Select
Selection.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*
""-""??_);_(@_)"
Columns("N").Select
Selection.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*
""-""??_);_(@_)"
Range("A1").Select
End Sub
Click to expand...
Click to expand...
 
D

Don Guillett

I just ran this simple test and got the same error msg with double
declaration.

Sub replaceinrng()
Dim c As Range
For Each c In Range("n2:n6")
c.Replace 0, "=a" & c.Row
Next c

'Dim c As Range' take OUT

For Each c In Range("q2:q6")
c.Replace 0, "=a" & c.Row
Next c


End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
rojobrown said:
I tried commenting out the 2nd dim cell as range. It still didn't work.
I'll try the one for the formatting too. Thanks.
--
Thanks a bunch!
rojobrown


Don Guillett said:
Help is not a good subject line for the archives. Try commenting out the
2nd
dim cell as range.
'Adds $ formatting

also try this
range("d1:l1,n1").entireColumn.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*

End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
rojobrown said:
I keep getting a compile error on the formula below and I can't figure
out
what I have wrong... The error comes up 'duplicate declaration in
current
scope' right under 'Creates Formula for LO Commission....Can you guys
help
me?

'Creates formula for Total Fee Income
Dim cell As Range
For Each cell In Range("L1:L100")
cell.Replace What:="0", Replacement:="=(E" & cell.Row & "+F" & cell.Row
&
_
"+G" & cell.Row & "+H" & cell.Row & ")-K" & cell.Row, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

'Creates Formula for LO Commission
Dim cell As Range
For Each cell In Range("N1:N100")
cell.Replace What:="0", Replacement:="=(L" & cell.Row & "*M" & cell.Row
&
_
")-J" & cell.Row, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False

Next
Range("J5").Select

'Adds $ formatting
Columns("D:D").Select
ActiveWindow.SmallScroll ToRight:=3
Columns("D:L").Select
Selection.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*
""-""??_);_(@_)"
Columns("N").Select
Selection.NumberFormat = _
"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]*
""-""??_);_(@_)"
Range("A1").Select
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Tidy up multiple find and replace code 2
.find with xlprevious 3
Need a type of lookup / find formula PLEASE 6
MACRO COLUMNS, SKIP ROWS 6
Macro to sort 1
MACRO PASTE CELL to COLUMN, SKIP ROWS 1
Changing cell content on n auto-filtered column 4
Macro help - add column selection 1

Top