How do I make this code shorter? Its Result should be given to var

J

Jay

Sub Try()

Dim x, a, BonRow, BonCol, Bon As Integer

Bon = ActiveCell.Row >= 2 And ActiveCell.Row <= 12
BonRow = 12
BonCol = 4

If Bon And (ActiveCell.Column >= 2 And ActiveCell.Column <= 4) Then
Cells(2, 4).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 5 And ActiveCell.Column <= 7) Then
Cells(2, 7).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 8 And ActiveCell.Column <= 10) Then
Cells(2, 10).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 11 And ActiveCell.Column <= 13) Then
Cells(2, 13).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 14 And ActiveCell.Column <= 16) Then
Cells(2, 16).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 17 And ActiveCell.Column <= 19) Then
Cells(2, 19).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 20 And ActiveCell.Column <= 22) Then
Cells(2, 22).Select
x = BonRow

End If
End If
End If
End If
End If
End If
End If
End If

Do

ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"

Loop Until ActiveCell.Row = x

End Sub
 
J

Jim Cone

What is the code supposed to do?
Are you a having problem with it? (If so, try removing one "End If")
--
Jim Cone
Portland, Oregon USA



"Jay"
<[email protected]>
wrote in message
Sub Try()

Dim x, a, BonRow, BonCol, Bon As Integer

Bon = ActiveCell.Row >= 2 And ActiveCell.Row <= 12
BonRow = 12
BonCol = 4

If Bon And (ActiveCell.Column >= 2 And ActiveCell.Column <= 4) Then
Cells(2, 4).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 5 And ActiveCell.Column <= 7) Then
Cells(2, 7).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 8 And ActiveCell.Column <= 10) Then
Cells(2, 10).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 11 And ActiveCell.Column <= 13) Then
Cells(2, 13).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 14 And ActiveCell.Column <= 16) Then
Cells(2, 16).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 17 And ActiveCell.Column <= 19) Then
Cells(2, 19).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 20 And ActiveCell.Column <= 22) Then
Cells(2, 22).Select
x = BonRow

End If
End If
End If
End If
End If
End If
End If
End If

Do

ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"

Loop Until ActiveCell.Row = x

End Sub
 
J

Jay

Jim Cone said:
What is the code supposed to do?
Are you a having problem with it? (If so, try removing one "End If")
--
Jim Cone
Portland, Oregon USA



"Jay"
<[email protected]>
wrote in message
Sub Try()

Dim x, a, BonRow, BonCol, Bon As Integer

Bon = ActiveCell.Row >= 2 And ActiveCell.Row <= 12
BonRow = 12
BonCol = 4

If Bon And (ActiveCell.Column >= 2 And ActiveCell.Column <= 4) Then
Cells(2, 4).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 5 And ActiveCell.Column <= 7) Then
Cells(2, 7).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 8 And ActiveCell.Column <= 10) Then
Cells(2, 10).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 11 And ActiveCell.Column <= 13) Then
Cells(2, 13).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 14 And ActiveCell.Column <= 16) Then
Cells(2, 16).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 17 And ActiveCell.Column <= 19) Then
Cells(2, 19).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 20 And ActiveCell.Column <= 22) Then
Cells(2, 22).Select
x = BonRow

End If
End If
End If
End If
End If
End If
End If
End If

Do

ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"

Loop Until ActiveCell.Row = x

End Sub

No actually this does works, the problem is that as I look at this like its only reiterating, so im figuring a way how to make this code short and easier to read.
Click to expand...
 
N

Nigel

As you posted it there is one too many EndIf statements, so it will not
work!

--

Regards,
Nigel
(e-mail address removed)



Jay said:
Jim Cone said:
What is the code supposed to do?
Are you a having problem with it? (If so, try removing one "End If")
--
Jim Cone
Portland, Oregon USA



"Jay"
<[email protected]>
wrote in message
Sub Try()

Dim x, a, BonRow, BonCol, Bon As Integer

Bon = ActiveCell.Row >= 2 And ActiveCell.Row <= 12
BonRow = 12
BonCol = 4

If Bon And (ActiveCell.Column >= 2 And ActiveCell.Column <= 4) Then
Cells(2, 4).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 5 And ActiveCell.Column <= 7) Then
Cells(2, 7).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 8 And ActiveCell.Column <= 10) Then
Cells(2, 10).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 11 And ActiveCell.Column <= 13) Then
Cells(2, 13).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 14 And ActiveCell.Column <= 16) Then
Cells(2, 16).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 17 And ActiveCell.Column <= 19) Then
Cells(2, 19).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 20 And ActiveCell.Column <= 22) Then
Cells(2, 22).Select
x = BonRow

End If
End If
End If
End If
End If
End If
End If
End If

Do

ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"

Loop Until ActiveCell.Row = x

End Sub

No actually this does works, the problem is that as I look at this like
its only reiterating, so im figuring a way how to make this code short
and easier to read.
Click to expand...
Click to expand...
 
J

Jim Cone

Actually your code will not run as it does not compile.
It has an extra End If. You must have a very compliant computer. <g>
You also did not say what the code is meant to do.
Nevertheless, I decided that it is designed to calculate the square
footage of the apartments you are remodeling. So this is your lucky day...
'--
Sub Try_R1()
Dim x As Long
Dim BonRow As Long
Dim BonCol As Long
Dim Bon As Long
Dim Rw As Long
Dim Cl As Long

Bon = ActiveCell.Row >= 2 And ActiveCell.Row <= 12
If Not Bon Then Exit Sub
BonRow = 12
BonCol = 4
x = BonRow
Cl = ActiveCell.Column

Select Case True
Case Cl >= 2 And Cl <= 4
Call MoreFormulas(Cells(2, 4), x)
Case Cl >= 5 And Cl <= 7
Call MoreFormulas(Cells(2, 7), x)
Case Cl >= 8 And Cl <= 10
Call MoreFormulas(Cells(2, 10), x)
Case Cl >= 11 And Cl <= 13
Call MoreFormulas(Cells(2, 13), x)
Case Cl >= 14 And Cl <= 16
Call MoreFormulas(Cells(2, 16), x)
Case Cl >= 17 And Cl <= 19
Call MoreFormulas(Cells(2, 19), x)
Case Cl >= 20 And Cl <= 22
Call MoreFormulas(Cells(2, 22), x)
End Select
End Sub
'--
Function MoreFormulas(ByRef rCell As Excel.Range, ByRef xx As Long)
Do
rCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"
Set rCell = rCell.Offset(1, 0)
Loop Until rCell.Row = xx
End Function
--
Jim Cone
Portland, Oregon USA





Jay said:
No actually this does works, the problem is that as I look at this like its only reiterating, so im figuring a way how to make
this code short and easier to read
Click to expand...




Jim Cone said:
What is the code supposed to do?
Are you a having problem with it? (If so, try removing one "End If")
Click to expand...



..
 
J

Jay

No actually this does works, the problem is that as I look at this like its
only reiterating, so im figuring a way how to make this code short and easier
to read.

Is it ok if I send you a file. its hard to explain.

Nigel said:
Your code as it stands fails, what are you trying to achieve?

--

Regards,
Nigel
(e-mail address removed)



Jay said:
Sub Try()

Dim x, a, BonRow, BonCol, Bon As Integer

Bon = ActiveCell.Row >= 2 And ActiveCell.Row <= 12
BonRow = 12
BonCol = 4

If Bon And (ActiveCell.Column >= 2 And ActiveCell.Column <= 4) Then
Cells(2, 4).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 5 And ActiveCell.Column <= 7) Then
Cells(2, 7).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 8 And ActiveCell.Column <= 10) Then
Cells(2, 10).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 11 And ActiveCell.Column <= 13) Then
Cells(2, 13).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 14 And ActiveCell.Column <= 16) Then
Cells(2, 16).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 17 And ActiveCell.Column <= 19) Then
Cells(2, 19).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 20 And ActiveCell.Column <= 22) Then
Cells(2, 22).Select
x = BonRow

End If
End If
End If
End If
End If
End If
End If
End If

Do

ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"

Loop Until ActiveCell.Row = x

End Sub
Click to expand...
Click to expand...
 
J

Jay

No actually this does works, the problem is that as I look at this like its
only reiterating, so im figuring a way how to make this code short and easier
to read.

Is it ok if I send you a file. its hard to explain.

Thanks.
Jay


Nigel said:
Your code as it stands fails, what are you trying to achieve?

--

Regards,
Nigel
(e-mail address removed)



Jay said:
Sub Try()

Dim x, a, BonRow, BonCol, Bon As Integer

Bon = ActiveCell.Row >= 2 And ActiveCell.Row <= 12
BonRow = 12
BonCol = 4

If Bon And (ActiveCell.Column >= 2 And ActiveCell.Column <= 4) Then
Cells(2, 4).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 5 And ActiveCell.Column <= 7) Then
Cells(2, 7).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 8 And ActiveCell.Column <= 10) Then
Cells(2, 10).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 11 And ActiveCell.Column <= 13) Then
Cells(2, 13).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 14 And ActiveCell.Column <= 16) Then
Cells(2, 16).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 17 And ActiveCell.Column <= 19) Then
Cells(2, 19).Select
x = BonRow
Else
If Bon And (ActiveCell.Column >= 20 And ActiveCell.Column <= 22) Then
Cells(2, 22).Select
x = BonRow

End If
End If
End If
End If
End If
End If
End If
End If

Do

ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"

Loop Until ActiveCell.Row = x

End Sub
Click to expand...
Click to expand...
 
J

Jay

Thanks Jim I'll try what you did if it'll work.

Regards,
Jay

Jim Cone said:
Actually your code will not run as it does not compile.
It has an extra End If. You must have a very compliant computer. <g>
You also did not say what the code is meant to do.
Nevertheless, I decided that it is designed to calculate the square
footage of the apartments you are remodeling. So this is your lucky day...
'--
Sub Try_R1()
Dim x As Long
Dim BonRow As Long
Dim BonCol As Long
Dim Bon As Long
Dim Rw As Long
Dim Cl As Long

Bon = ActiveCell.Row >= 2 And ActiveCell.Row <= 12
If Not Bon Then Exit Sub
BonRow = 12
BonCol = 4
x = BonRow
Cl = ActiveCell.Column

Select Case True
Case Cl >= 2 And Cl <= 4
Call MoreFormulas(Cells(2, 4), x)
Case Cl >= 5 And Cl <= 7
Call MoreFormulas(Cells(2, 7), x)
Case Cl >= 8 And Cl <= 10
Call MoreFormulas(Cells(2, 10), x)
Case Cl >= 11 And Cl <= 13
Call MoreFormulas(Cells(2, 13), x)
Case Cl >= 14 And Cl <= 16
Call MoreFormulas(Cells(2, 16), x)
Case Cl >= 17 And Cl <= 19
Call MoreFormulas(Cells(2, 19), x)
Case Cl >= 20 And Cl <= 22
Call MoreFormulas(Cells(2, 22), x)
End Select
End Sub
'--
Function MoreFormulas(ByRef rCell As Excel.Range, ByRef xx As Long)
Do
rCell.FormulaR1C1 = "=(RC[-2])+(RC[-1])"
Set rCell = rCell.Offset(1, 0)
Loop Until rCell.Row = xx
End Function
--
Jim Cone
Portland, Oregon USA





Jay said:
No actually this does works, the problem is that as I look at this like its only reiterating, so im figuring a way how to make
this code short and easier to read
Click to expand...




Jim Cone said:
What is the code supposed to do?
Are you a having problem with it? (If so, try removing one "End If")
Click to expand...



..
Click to expand...
 
J

Jay

Hi Jim Cone,

i find your code useful but I haven't use it yet. Anyway I have a program
here that was created by someone and its intended for me to analyze the this
system, but with my knowledge I guess It'll take so much time for me
analyzing it and its urgently needed to be updated. You are very fluent with
function/s, almost the entire program was created with function. Can I seek
your help regarding this?

Thank you very much.

Regards,
Jay
 
D

Dana DeLouis

How do I make this code shorter?

Would this work?

Sub Try()
Dim Rw As Long
Dim Cl As Long
Dim C As Long
Dim Valid As Boolean

With ActiveCell
Rw = .Row
Cl = .Column
End With

Valid = Rw >= 2 And Rw <= 12 And _
Cl >= 2 And Cl <= 22

If Not Valid Then Exit Sub

'// Get new column
C = 3 * Int((Cl + 4) / 3) - 2

'// Fill in Formula down to row 12
Range(Cells(Rw, C), Cells(12, C)).FormulaR1C1 = "=RC[-2]+RC[-1]"
End Sub

= = =
HTH
Dana DeLouis
 
J

Jim Cone

Dana,
I did say it was Jay's lucky day. <g>
--
Jim Cone
Portland, Oregon USA


"Dana DeLouis"
How do I make this code shorter?
Click to expand...

Would this work?

Sub Try()
Dim Rw As Long
Dim Cl As Long
Dim C As Long
Dim Valid As Boolean

With ActiveCell
Rw = .Row
Cl = .Column
End With

Valid = Rw >= 2 And Rw <= 12 And _
Cl >= 2 And Cl <= 22

If Not Valid Then Exit Sub

'// Get new column
C = 3 * Int((Cl + 4) / 3) - 2

'// Fill in Formula down to row 12
Range(Cells(Rw, C), Cells(12, C)).FormulaR1C1 = "=RC[-2]+RC[-1]"
End Sub

= = =
HTH
Dana DeLouis
 
J

Jim Cone

Make a brand new separate post and somebody will be along to help.

This contains good advice about asking newsgroup questions...
http://support.microsoft.com/KB/555375
--
Jim Cone
Portland, Oregon USA


"Jay"
<[email protected]>
wrote in message
Hi Jim Cone,
i find your code useful but I haven't use it yet. Anyway I have a program
here that was created by someone and its intended for me to analyze the this
system, but with my knowledge I guess It'll take so much time for me
analyzing it and its urgently needed to be updated. You are very fluent with
function/s, almost the entire program was created with function. Can I seek
your help regarding this?
Thank you very much.
Regards,
Jay
 
D

Dana DeLouis

C = 3 * Int((Cl + 4) / 3) - 2

Slightly better might be just an offset equation...

Sub Try2()
Dim Rw As Long
Dim Cl As Long
Dim C As Long

Rw = ActiveCell.Row
Cl = ActiveCell.Column

If Not (Rw >= 2 And Rw <= 12 And _
Cl >= 2 And Cl <= 22) Then Exit Sub

'// Offset by 2,1,or 0
C = Cl + 2 - (Cl + 1) Mod 3

'// Fill in Formula down to row 12
Range(Cells(Rw, C), Cells(12, C)).FormulaR1C1 = "=RC[-2]+RC[-1]"
End Sub

- - -
HTH :>)
Dana DeLouis
 
D

Dana DeLouis

How do I make this code shorter?

Maybe not as clear, but...

Sub Try4()
Dim R As Long
Dim C As Long

R = ActiveCell.Row
C = ActiveCell.Column

If Not (R >= 2 And R <= 12 And _
C >= 2 And C <= 22) Then Exit Sub

'// Fill in Formula down to row 12
Cells(R, C + 2 - (C + 1) Mod 3).Resize(12 - R + 1) = "=RC[-2]+RC[-1]"
End Sub


= = =
HTH :>)
Dana DeLouis
 
J

Jay

Hey thanks this is the exact one ,Im trying to modify the values of this code
"C = Cl + 2 - (Cl + 1) Mod 3" so it will compute after 3 columns. but I cant
get the exact value. Need help thanks.

Jay


Dana DeLouis said:
C = 3 * Int((Cl + 4) / 3) - 2
Click to expand...

Slightly better might be just an offset equation...

Sub Try2()
Dim Rw As Long
Dim Cl As Long
Dim C As Long

Rw = ActiveCell.Row
Cl = ActiveCell.Column

If Not (Rw >= 2 And Rw <= 12 And _
Cl >= 2 And Cl <= 22) Then Exit Sub

'// Offset by 2,1,or 0
C = Cl + 2 - (Cl + 1) Mod 3

'// Fill in Formula down to row 12
Range(Cells(Rw, C), Cells(12, C)).FormulaR1C1 = "=RC[-2]+RC[-1]"
End Sub

- - -
HTH :>)
Dana DeLouis
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

VBA triggers on tabkey 8
boolean to control sort order 0
How do I trapped the error and optimize the code? 5
I want to loop this macro 2
Cell Address References won't update when Sorting 2
Excel hangs 1
Value not shown on selected ActiveCell 1
Custom Fill in Listbox 2

Top