Take value from active cell to make formula

[Sjaakve]

Hi,

I am making a sheet with a language function. four languages can be
chosen. I am using excels OFFSET function to search the correct word
from a small database. Now i have to type the complete formula every
time, while only one value is different.

an example of the formula is:

=OFFSET(A1;X;1) where only x changes.

how can i just type the number for x in a cell, start a macro, and the
complete fomula appears in the cell?

i recorded myself typing in the formula, but i dont know how to
include the value already present in de cell.

Thanx,

Sjaak

 

[Jacob Skaria]

Do you mean something like this.. From row 1 to 100 of Column A the formula
will be applied. The x value will be taken from the respective cell..........

Sub Macro()
Dim lngRow as Long
For lngRow = 1 to 100
Range("A" & lngRow).Formula = "=Offset(A1," & Cells(lngRow,lngCol) & ",1)"
Next
End Sub

If this post helps click Yes

 

[Don Guillett]

Right click sheet tab>view code>insert this>Now, when you change ANY cell
the magic formula will appear in that cell. So you probably want to restrict
to a range

Private Sub Worksheet_Change(ByVal Target As Range)
mv = Target
Application.EnableEvents = False
'us
'Target.Formula = "=offset(a1," & mv & ",1)"

Target.Formula = "=offset(a1;" & mv & ";1)"
Application.EnableEvents = True
End Sub

 

[Gary''s Student]

Here is an example you can adapt for your needs. Say we want to insert
formulas in column A like:

=OFFSET(C7,2,3,1,1)

But all we wnt to type is the 7

Paste the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set A = Range("A:A")
If Intersect(t, A) Is Nothing Then Exit Sub
s1 = "=OFFSET(C"
s3 = ",2,3,1,1)"
s2 = t.Value
Application.EnableEvents = False
t.Value = s1 & s2 & s3
Application.EnableEvents = True
End Sub

Just type an integer in column A and the formula will appear.

 

[Sjaakve]

Jacob,
thanx for the suggestion, but it does not work.

 

[Sjaakve]

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?

 

[Don Guillett]

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

 

[Sjaakve]

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.

 

[Don Guillett]

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

 

[Sjaakve]

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

 

[Don Guillett]

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