Take value from active cell to make formula

  • Thread starter Thread starter Sjaakve
  • Start date Start date
S

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
 
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
 
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
 
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.
 
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?
 
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.
 
Sub changefrombutton()
ActiveCell.Formula = "=offset(a1," & ActiveCell & ",2)"
End Sub
 
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
 
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
 
Back
Top