| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make this code shorter? Its Result should be given to var
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to var
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" 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to var
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to
"Jim Cone" wrote: 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" 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to
|
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to
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" wrote in message 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 "Jim Cone" wrote: 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 .. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to
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" wrote: Your code as it stands fails, what are you trying to achieve? -- Regards, Nigel "Jay" 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to
Thanks Jim I'll try what you did if it'll work.
Regards, Jay "Jim Cone" wrote: 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" wrote in message 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 "Jim Cone" wrote: 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 .. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to var
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 "Jay" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given tovar
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 Jay wrote: 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to var
Dana, I did say it was Jay's lucky day. <g -- Jim Cone Portland, Oregon USA "Dana DeLouis" wrote in message 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to var
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" 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given tovar
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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given tovar
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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make this code shorter? Its Result should be given to
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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I Make This Computation Shorter? | Excel Discussion (Misc queries) | |||
| Shorter Code | Excel Programming | |||
| How to make a contingent formula shorter... | Excel Discussion (Misc queries) | |||
| I wonder if you can make this shorter | Excel Worksheet Functions | |||
| Shorter code | Excel Programming | |||
Linear Mode
