Need help with a Macro

  • Thread starter JakeShipley2008
  • Start date
J

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.
 
S

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
L

L. Howard Kittle

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
 
G

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
 
L

L. Howard Kittle

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
 
J

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

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
 
R

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
 
Top