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

  • Thread starter Thread starter Jay
  • Start date Start date
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
 
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
 
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...
 
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...
 
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...



..
 
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...
 
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...
 
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...
 
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
 
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
 
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
 
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
 
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
 
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
 
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
How do I trapped the error and optimize the code? 5
Cell Address References won't update when Sorting 2
Selecting a certain cell 5
I want to loop this macro 2
Value not shown on selected ActiveCell 1
What Does This Code Do? 7
Looping through folders and using the first file in each folder 2

Back
Top