Excel Macro Help

[paul_kingtiger]

Hello,
I'm trying to get a macro to run whenever new data is entered in a cell. I want this to work for every cell in a row.

When active the macro updates various cells on the same row with data (in this case formulas.

The bit I'm having trouble with is getting the macro to work when the cell is updated.

I've been looking through various forums to no avail.

Thanks in advance for any help.

Paul

Private Sub Worksheet_Calculate(ByVal Target As Range)

' Turns off Screen Update
Application.ScreenUpdating = False

If Not Intersect(Target, Range("K:K")) Is Nothing Then

' Imputs forulas to a number of cells on the same row as the target cell
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],Material,1,0))=FALSE,VLOOKUP(RC[-1],MaterialTypeID,2,0),"""")"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],Material,1,0)) = TRUE, ""N"","""")"
ActiveCell.Offset(0, 8).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]=""N"",RC[-9],"""")"
ActiveCell.Offset(0, 14).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>1,""Yes"","""")"
ActiveCell.Offset(0, -23).Select
End If

' Turns on Screen Update
Application.ScreenUpdating = True

End Sub

[paul_kingtiger]

Never mind I've fixed it. Slight error in the code, should be Worksheet_Change not Worksheet_Calculate

Private Sub Worksheet_change(ByVal Target As Excel.Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

If Target.Column = 11 Then

' Imputs forulas to a number of cells on the same row as the target cell
ActiveCell.Offset(0, 0).Select
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],Material,1,0))=FALSE,VLOOKUP(RC[-1],MaterialTypeID,2,0),"""")"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],Material,1,0)) = TRUE, ""N"","""")"
ActiveCell.Offset(0, 8).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]=""N"",RC[-9],"""")"
ActiveCell.Offset(0, 14).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>1,""Yes"","""")"
ActiveCell.Offset(0, -23).Select
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

[NooNoo]

Always something small with code isn't it!

[paul_kingtiger]

I've come across another problem someone might be able to help with.

I have a Column full of data and I want to run an If / Else statement in the macro based on the contents of the cells in the column. This is so I can only run some code if the value appears in the column.


Example:

If Column L contains "Server" then


Else

I've had a look around but can only find the syntax for a cell formula rather than a macro.

[NooNoo]

What did you want to do with the result... you might not need a macro - Vlookup would do the job... or programmatically something like this? Or did you need a count of how many times server appears? For that you can use countif.

[paul_kingtiger]

I've got a macro that sorts the data by the L column then finds the first cell with "Server". It then copies some data to another worksheet.

The problem is there isn't always a cell with "Server" in and when it tries to run the find it gets upset.

What I want is a way to prevent it getting stuck, either by telling it to just carry on if it can't find "Server" or skipping the find completely if there isn't a cell with "Server" in the column.

Code below:

Sub PopDIServer()

' Paul Kingtiger - 10/12/2008
' Populates the Data Intergration Sheets

' Status Update
Application.DisplayStatusBar = True
Application.StatusBar = "Populating Server Data Integration sheet"

' Turns off Screen Update
Call DebugMode

' Resets Cursor and begins population
Sheets("Server").Select
Range("A2").Select

' Advances to First Server Row
Sheets("ASSET").Select
Columns("L:L").Select


Selection.Find(What:="Server", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate

Do While ActiveCell = "Server"

Lots of code in here, copying values from cell to cell

Loop

' Sorts the sheets and cleans the view
Sheets("Server").Select
Cells.Select
Selection.Columns.AutoFit
Columns("A:N").Select
ActiveWindow.Zoom = True
Range("A2").Select

' Restores Curson and Statusbar
Application.StatusBar = False

' Turns on Screen Update
Application.ScreenUpdating = True

End Sub

[NooNoo]

Selection.Find(What:="Server", After:=ActiveCell, LookIn:=xlValues, _
You need to trap what happens if selection is blank or check for server in the column first and if it is, then do Selection.Find(What:="Server", After:=ActiveCell, LookIn:=xlValues, _

[paul_kingtiger]

Hello all, thought I'd necro this one rather than start a new thread.

I'm trying to write a little bit of code that does one of two things depending on the contents of a cell.

Essentiall if the value of a cell is "Cabinet" then I want to run the first bit of code, otherwise the second. Everything works correctly except the first line. Can anyone help me with what I'm doing wrong?


If ActiveCell.Offset(0, -6).Value = "Cabinet" Then
ActiveCell.Offset(0, 24).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-25]=""Rack 19 inch"",444.5,"""")"
Call CommonPasteValue
ActiveCell.Offset(0, -24).Select
Else
' -------------------------------------------------
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=""Rack 19 inch"",444.5,"""")"
Call CommonPasteValue
ActiveCell.Offset(0, -2).Select
End If



EDIT

Never mind, I found the problem. There was an unwanted space after the value "Cabinet" which was messing up the ActiveCell =