Need help with a Macro
 

I was hoping someone could help me out???

I am trying to make a macro to do the following for example:

If the user types 4 in Cell A1 - then cell B1 will =1, B2=2, B3=3, B4=4. I
am trying to get this work with any number that might be entered into cell A1
- not exceed 180.

Thanks in advance.
--
Jake

Need help with a Macro
 

What do you want in B1 for other numbers entered into A1?

Is there any relationship betwen the two?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"JakeShipley2008" wrote in
message ...
I was hoping someone could help me out???

I am trying to make a macro to do the following for example:

If the user types 4 in Cell A1 - then cell B1 will =1, B2=2, B3=3, B4=4. I
am trying to get this work with any number that might be entered into cell
A1
- not exceed 180.

Thanks in advance.
--
Jake

Need help with a Macro
 

Hi Jake,

Give this a try.

Sub NumberIt()
Dim i As Integer
i = Range("A1").Value
If i 180 Then
MsgBox "Greater than 180"
Exit Sub
End If

For i = 1 To i - 1
Range("B1").Select
ActiveCell.Value = 1
ActiveCell.Offset(i, 0).Value = ActiveCell.Value + i
Next
End Sub

HTH
Regards,
Howard

"JakeShipley2008" wrote in
message ...
I was hoping someone could help me out???

I am trying to make a macro to do the following for example:

If the user types 4 in Cell A1 - then cell B1 will =1, B2=2, B3=3, B4=4. I
am trying to get this work with any number that might be entered into cell
A1
- not exceed 180.

Thanks in advance.
--
Jake

Need help with a Macro
 

Jake

Expanding a bit on Howard's code.

You could alter it to be sheet event code which would run whenever a number is
entered in A1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
i = Target.Value
If i 180 Then
MsgBox "Greater than 180"
goto endit
End If
Columns(2).ClearContents
For i = 1 To i - 1
Range("B1").Select
With ActiveCell
.Value = 1
.Offset(i, 0).Value = ActiveCell.Value + i
End With
Next
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 2 Jun 2008 14:15:39 -0700, "L. Howard Kittle"
wrote:

Hi Jake,

Give this a try.

Sub NumberIt()
Dim i As Integer
i = Range("A1").Value
If i 180 Then
MsgBox "Greater than 180"
Exit Sub
End If

For i = 1 To i - 1
Range("B1").Select
ActiveCell.Value = 1
ActiveCell.Offset(i, 0).Value = ActiveCell.Value + i
Next
End Sub

HTH
Regards,
Howard

"JakeShipley2008" wrote in
message ...
I was hoping someone could help me out???

I am trying to make a macro to do the following for example:

If the user types 4 in Cell A1 - then cell B1 will =1, B2=2, B3=3, B4=4. I
am trying to get this work with any number that might be entered into cell
A1
- not exceed 180.

Thanks in advance.
--
Jake

Need help with a Macro
 

Thank you very much that is exactly what I needed!!!
--
Jake


"Gord Dibben" wrote:

Jake

Expanding a bit on Howard's code.

You could alter it to be sheet event code which would run whenever a number is
entered in A1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
i = Target.Value
If i 180 Then
MsgBox "Greater than 180"
goto endit
End If
Columns(2).ClearContents
For i = 1 To i - 1
Range("B1").Select
With ActiveCell
.Value = 1
.Offset(i, 0).Value = ActiveCell.Value + i
End With
Next
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 2 Jun 2008 14:15:39 -0700, "L. Howard Kittle"
wrote:

Hi Jake,

Give this a try.

Sub NumberIt()
Dim i As Integer
i = Range("A1").Value
If i 180 Then
MsgBox "Greater than 180"
Exit Sub
End If

For i = 1 To i - 1
Range("B1").Select
ActiveCell.Value = 1
ActiveCell.Offset(i, 0).Value = ActiveCell.Value + i
Next
End Sub

HTH
Regards,
Howard

"JakeShipley2008" wrote in
message ...
I was hoping someone could help me out???

I am trying to make a macro to do the following for example:

If the user types 4 in Cell A1 - then cell B1 will =1, B2=2, B3=3, B4=4. I
am trying to get this work with any number that might be entered into cell
A1
- not exceed 180.

Thanks in advance.
--
Jake

Need help with a Macro
 

Hi Gord and Jake,

Gord, when I saw you responded to Jakes post, I told my wife... "...come see
this", Gord will blow the "Range("B1").Select" away with some eloquent code
and make this a symphony with a tiny bit of code. (You seldom need to
"Select" ... to get stuff done...) I'm pleased so much of my code exists in
the much more efficent offer you made.

I'm always looking over your shoulder and other MVP's for knowledge.

Way FUN... However I do not make a living at it, it's a hobby.

Regards,
Howard

"JakeShipley2008" wrote in
message ...
I was hoping someone could help me out???

I am trying to make a macro to do the following for example:

If the user types 4 in Cell A1 - then cell B1 will =1, B2=2, B3=3, B4=4. I
am trying to get this work with any number that might be entered into cell
A1
- not exceed 180.

Thanks in advance.
--
Jake

Need help with a Macro
 

It is funny, I was just telling my wife how helpful people on this site are.
I am by no means an expert - just piddle with it from time to time. Everyone
on here has been very nice when needed. It is refreshing to see that people
out there are still willing to help.

Thanks to all!!
--
Jake


"L. Howard Kittle" wrote:

Hi Gord and Jake,

Gord, when I saw you responded to Jakes post, I told my wife... "...come see
this", Gord will blow the "Range("B1").Select" away with some eloquent code
and make this a symphony with a tiny bit of code. (You seldom need to
"Select" ... to get stuff done...) I'm pleased so much of my code exists in
the much more efficent offer you made.

I'm always looking over your shoulder and other MVP's for knowledge.

Way FUN... However I do not make a living at it, it's a hobby.

Regards,
Howard

"JakeShipley2008" wrote in
message ...
I was hoping someone could help me out???

I am trying to make a macro to do the following for example:

If the user types 4 in Cell A1 - then cell B1 will =1, B2=2, B3=3, B4=4. I
am trying to get this work with any number that might be entered into cell
A1
- not exceed 180.

Thanks in advance.
--
Jake

Need help with a Macro
 

Thanks Howard.

But notice I still used the select for Range("B1") so remains an unfinished
symphony.

Amend to....................

For i = 1 To i - 1
With Range("B1")
.Value = 1
.Offset(i, 0).Value = .Value + i
End With
Next

Gord

On Mon, 2 Jun 2008 18:38:51 -0700, "L. Howard Kittle"
wrote:

Hi Gord and Jake,

Gord, when I saw you responded to Jakes post, I told my wife... "...come see
this", Gord will blow the "Range("B1").Select" away with some eloquent code
and make this a symphony with a tiny bit of code. (You seldom need to
"Select" ... to get stuff done...) I'm pleased so much of my code exists in
the much more efficent offer you made.

I'm always looking over your shoulder and other MVP's for knowledge.

Way FUN... However I do not make a living at it, it's a hobby.

Regards,
Howard

"JakeShipley2008" wrote in
message ...
I was hoping someone could help me out???

I am trying to make a macro to do the following for example:

If the user types 4 in Cell A1 - then cell B1 will =1, B2=2, B3=3, B4=4. I
am trying to get this work with any number that might be entered into cell
A1
- not exceed 180.

Thanks in advance.
--
Jake

Need help with a Macro
 

Maybe it's just the glasses of liquer muscat, but why can't you just put
the formula =IF(AND($A$10,$A$1<181)=TRUE,$A$1-3,"") in B1, and the
appropriate formula in B2, B3, B4 ?

Rob


"JakeShipley2008" wrote in
message ...
I was hoping someone could help me out???

I am trying to make a macro to do the following for example:

If the user types 4 in Cell A1 - then cell B1 will =1, B2=2, B3=3, B4=4. I
am trying to get this work with any number that might be entered into cell
A1
- not exceed 180.

Thanks in advance.
--
Jake