Take value from active cell to make formula
 

What I sent does that without hitting the button...

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sjaakve" wrote in message
...
Thanks for all the efforts,

I was thinking about something like this.

1 - type "4" in to a cell, can be any cell.
2 - hit a button, or a keystroke
3 - "4" changes into "=OFFSET(MAT!A1;4,1)" , where the 4 in the
formule is the 4 i've typed before starting the macro.

Is this possible?

Take value from active cell to make formula
 

Ok, it took me while to figure out how the sent macro works. Thans
again.

I got it to work. It works ok, but i wanted it to work with a button.
I have to change numbers in that sheet all the time. The restriction
would be to large for the macro the save time.

like this:
1 - type "4" in to a cell, can be any cell.
2 - hit a button, or a keystroke
3 - "4" changes into "=OFFSET(MAT!A1;4,1)" , where the 4 in the
formule is the 4 i've typed before starting the macro.

i've tried to rewrite your macro but i can't get it to work.

Take value from active cell to make formula
 

Sub changefrombutton()
ActiveCell.Formula = "=offset(a1," & ActiveCell & ",2)"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sjaakve" wrote in message
...
Ok, it took me while to figure out how the sent macro works. Thans
again.

I got it to work. It works ok, but i wanted it to work with a button.
I have to change numbers in that sheet all the time. The restriction
would be to large for the macro the save time.

like this:
1 - type "4" in to a cell, can be any cell.
2 - hit a button, or a keystroke
3 - "4" changes into "=OFFSET(MAT!A1;4,1)" , where the 4 in the
formule is the 4 i've typed before starting the macro.

i've tried to rewrite your macro but i can't get it to work.

Take value from active cell to make formula
 

works beautifully,

thanx.

I made some changes of my own. Took me all day to do that. posted
below.

Sub Lan()
Dim offset As Integer
For Each c In Selection
c.Select
ActiveCell.Select
If IsNumeric(ActiveCell.Value) Then
offset = ActiveCell.Value
ActiveCell = "=PROPER(OFFSET(LAN!A1," & offset & ",B1))"
With Selection.Interior
.Color = 5296274
End With
End If
Next c
End Sub

Now the macro work when multiple cells are selected, and when selected
cells are merged.

Hope you like it.

grts,

Sjaak

Take value from active cell to make formula
 

Try this instead where the only selection is the range to change.
Change interior color index number to suit.

Sub chnagefrombuttonmultiple()
Dim c As Range
For Each c In Selection ' or range("a2:a6") for NO selection
If IsNumeric(c) Then
c.Formula = "=offset(lan!a1," & c.value & ",b1)"
c.Interior.ColorIndex = 3
End If
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sjaakve" wrote in message
...
works beautifully,

thanx.

I made some changes of my own. Took me all day to do that. posted
below.

Sub Lan()
Dim offset As Integer
For Each c In Selection
c.Select
ActiveCell.Select
If IsNumeric(ActiveCell.Value) Then
offset = ActiveCell.Value
ActiveCell = "=PROPER(OFFSET(LAN!A1," & offset & ",B1))"
With Selection.Interior
.Color = 5296274
End With
End If
Next c
End Sub

Now the macro work when multiple cells are selected, and when selected
cells are merged.

Hope you like it.

grts,

Sjaak