Help with Macro already written

[woody1983]

I have the following macro written which follows the purpose of what I want it to do:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("a2").Select
Dim rng As Range
If Target.Count > 1 Then Exit Sub
Set rng = Range("A1")
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Value = 1 Then
Application.GoTo Sheet2.Range("b1:r1")
Selection.Copy
Sheets("Sheet1").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End If
End Sub

I have a formula in cell A1 which does the following:

=IF(ISERROR(IF(VLOOKUP($E$1,$K$4:$K$5,1,0)=$E$1,1,"")),"",IF(VLOOKUP($E$1,$K$4:$K$5,1,0)=$E$1,1,""))

However even with calculations on I have to go into this cell and select the formulae and hit return for it to run the macro.

I wondered if there was a way I could either write this into the macro:

Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(IF(VLOOKUP(R1C5,R4C11:R5C11,1,0)=R1C5,1,"""")),"""",IF(VLOOKUP(R1C5,R4C11:R5C11,1,0)=R1C5,1,""""))"
Range("A2").Select

or if this was even possible?

 

[saltkev]

Hi

Give this a go

Range("A1").Formula = "Yourformula"

 

[woody1983]

where would I put this within the macro?

 

[woody1983]

Apologies my Macro currently looks like;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
If Target.Count > 1 Then Exit Sub
Set rng = Range("A1")
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Value = 1 Then
Application.GoTo Sheet2.Range("b1:r1")
Selection.Copy
Sheets("Sheet1").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End If
End Sub

 

[saltkev]

Hi

The Spread sheet event "Worksheet_Change(ByVal Target As Excel.Range)" only runs when a change is made to your spread sheet, specifically Range("A1") because of the intersection. This means that nothing will happen until a change is made to "A1". I figure that is why you are having to actuate the cell.

Perhaps concider moving the macro to a Worksheet_SelectionChange(ByVal Target As Range) event and limiting the range as you already have with INTERSECT to perhaps A1 and A2 then when you move in these cells the macro will run.

Dim isect As Excel.Range
Set isect = Application.Intersect(Range(ActiveCell.Address), Range("a1:a2"))
If isect Is Nothing Then
Else

Dim rng As Range
If Target.Count > 1 Then Exit Sub
Set rng = Range("A1")
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Value = 1 Then
Application.GoTo Sheet2.Range("b1:r1")
Selection.Copy
Sheets("Sheet1").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End If
 

End If

regards

Kev

 

[woody1983]

Thanks, great answer

 

[woody1983]

Apologies but I can't get this to work?

 

[saltkev]

Hi

Try Adding after your Dim rng As Range


sheets("sheet1").Range("a1").formula = "=yourformula"


Kev