Need help with a Macro

[JakeShipley2008]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gord Dibben]

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

 

[JakeShipley2008]

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

 

[JakeShipley2008]

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!!

 

[Gord Dibben]

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

 

[Rob L]

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

Rob