PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > How do I make this code shorter? Its Result should be given to var

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

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

 
 
Jay
Guest
Posts: n/a
 
      8th Jan 2009
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
 
Reply With Quote
 
 
 
 
Jim Cone
Guest
Posts: n/a
 
      8th Jan 2009
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"
<(E-Mail Removed)>
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
 
Reply With Quote
 
Nigel
Guest
Posts: n/a
 
      8th Jan 2009
Your code as it stands fails, what are you trying to achieve?

--

Regards,
Nigel
(E-Mail Removed)



"Jay" <(E-Mail Removed)> wrote in message
news:91514E2C-A6DC-4A10-BDB0-(E-Mail Removed)...
> 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
>
 
Reply With Quote
 
Jay
Guest
Posts: n/a
 
      8th Jan 2009


"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"
> <(E-Mail Removed)>
> 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.
 
Reply With Quote
 
Nigel
Guest
Posts: n/a
 
      8th Jan 2009
As you posted it there is one too many EndIf statements, so it will not
work!

--

Regards,
Nigel
(E-Mail Removed)



"Jay" <(E-Mail Removed)> wrote in message
news:B70B82AD-55E7-46E1-A22B-(E-Mail Removed)...
>
>
> "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"
>> <(E-Mail Removed)>
>> 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.
 
Reply With Quote
 
Jim Cone
Guest
Posts: n/a
 
      8th Jan 2009

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" <(E-Mail Removed)>
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



..
 
Reply With Quote
 
Jay
Guest
Posts: n/a
 
      8th Jan 2009
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" wrote:

> Your code as it stands fails, what are you trying to achieve?
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:91514E2C-A6DC-4A10-BDB0-(E-Mail Removed)...
> > 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
> >
>
>
 
Reply With Quote
 
Jay
Guest
Posts: n/a
 
      8th Jan 2009
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
> (E-Mail Removed)
>
>
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:91514E2C-A6DC-4A10-BDB0-(E-Mail Removed)...
> > 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
> >
>
>
 
Reply With Quote
 
Jay
Guest
Posts: n/a
 
      8th Jan 2009
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" <(E-Mail Removed)>
> 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
>
>
>
> ..
>
>
 
Reply With Quote
 
Jay
Guest
Posts: n/a
 
      8th Jan 2009
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
>
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« Applying worksheet formatting to all sheets in workbook | Filter range based on results of multiple selections in a listbox »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
I wonder if you can make this shorter =?Utf-8?B?VA==?= Microsoft Excel Worksheet Functions 0 23rd Dec 2004 08:47 PM
Re: make this shorter please! Ken Wright Microsoft Excel Worksheet Functions 14 8th Sep 2003 10:25 AM
Re: make this shorter please! CLR Microsoft Excel Worksheet Functions 0 6th Sep 2003 10:30 PM
Re: make this shorter please! Aladin Akyurek Microsoft Excel Worksheet Functions 0 6th Sep 2003 10:11 PM
Re: make this shorter please! Don Guillett Microsoft Excel Worksheet Functions 1 6th Sep 2003 09:12 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 03:44 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top