Expert: Tom Ogilvy - 7/15/2009

Question
QUESTION: Current coding is:

For y = 3 To 1000
If Sheets("Gas Pending").Cells(y, 1) = "" Then Sheets("Gas Pending").Cells(y, 18) = "" _
Else Sheets("Gas Pending").Cells(y, 18).FormulaR1C1 = "=TODAY()-RC[-10]"
Next y
       Range("R3:R1000").Copy
       Range("R3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False

I would like to not have to paste special and instead just input the value of the R1C1 formula.

ANSWER: Ryan,


For y = 3 To 1000
If Sheets("Gas Pending").Cells(y, 1) = "" Then

 Sheets("Gas Pending").Cells(y, 18) = ""

Else

v = Date - Sheets("Gas Pending").Cells(y,8).Value
Sheets("Gas Pending").Cells(y, 18).Value = v

End if

Next y

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Thanks so much for your previous help. One more quick question.

Here is my current code:

'For b = 3 To 1000
'If Sheets("Gas Pending").Cells(b, 1) = "" Then Sheets("Gas Pending").Cells(b, 26) = "" _
'Else Sheets("Gas Pending").Cells(b, 26).FormulaR1C1 = "=TODAY()-RC[-17]"
'Next b
'       Range("Z3:Z1000").Copy
 '      Range("Z3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  '     :=False, Transpose:=False

'For X = 3 To 1000
'If Sheets("Gas Pending").Cells(X, 9) = "" Then Sheets("Gas Pending").Cells(X, 22) = "" _
'Else Sheets("Gas Pending").Cells(X, 22).FormulaR1C1 = "=If(RC[+4]<90,""0-3"",IF(RC[+4]<180,""3-6"","">6""))"
'Next X
'       Range("V3:v1000").Copy
 '      Range("V3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  '     :=False, Transpose:=False
   
'Application.CutCopyMode = False

I would like to simplify this so it doesnt have to paste special and it just inputs the values into the cells. preferably take it down to one  "If" statement.

ANSWER: Ryan,

Since you are checking two separate conditions, you need two if statements.  You can reduce it down to on pass through the rows (one loop).


For b = 3 To 1000
If Sheets("Gas Pending").Cells(b, 1) = "" Then
  Sheets("Gas Pending").Cells(b, 26) = ""
Else
  Sheets("Gas Pending").Cells(b, 26).FormulaR1C1 = _
     "=TODAY()-RC[-17]"
end if
If Sheets("Gas Pending").Cells(b, 9) = "" Then
  Sheets("Gas Pending").Cells(b, 22) = ""
Else
  Sheets("Gas Pending").Cells(b, 22).FormulaR1C1 = _
  "=If(RC[+4]<90,""0-3"",IF(RC[+4]<180,""3-6"","">6""))"
Next b

With Range("Z3:Z1000")
 .Formula = .Value
End with
With Range("V3:V1000")
 .Formula = .Value
End with
 
Application.CutCopyMode = False

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: I think this should be the last one.

I need to run the below macro across several worksheets. Here is what I have so far:

Sub GasCalc()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Gas Pend" Or ws.Name = "FP-Pend" Or ws.Name = "ARP" Or ws.Name = "Power-Pend" Or ws.Name = "Other-Pend" Then

For a = 3 To 1000
If Cells(a, 1) = "" Then
Cells(a, 19) = ""

Else

v = Date - Cells(a, 8).Value
Cells(a, 19).Value = v

End If

Next a

For b = 3 To 1000
If Cells(b, 9) = "" Then
Cells(b, 23) = ""
   
Else

v = 90 - (Date - Cells(b, 9).Value)
Cells(b, 23).Value = v

End If
Next b

End If
Next ws

End Sub

The problem is that it is not running across the five named worksheets, mearly running 5 times on the active worksheet. Any solutions?  

Answer
Ryan,

the unqualified Cells(a, 1)  refers to the activesheet (and thus your problem).  
You could either use

ws.Cells(a, 1)

and do this for all cell references

or the easy fix is to activate the sheet you are working on

Sub GasCalc()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Gas Pend" Or ws.Name = "FP-Pend" Or ws.Name = "ARP" Or ws.Name = "Power-Pend" Or ws.Name = "Other-Pend" Then

ws.Activate   '<=== activate the sheet


For a = 3 To 1000
If Cells(a, 1) = "" Then
Cells(a, 19) = ""

Else

v = Date - Cells(a, 8).Value
Cells(a, 19).Value = v

End If

Next a

For b = 3 To 1000
If Cells(b, 9) = "" Then
Cells(b, 23) = ""
  
Else

v = 90 - (Date - Cells(b, 9).Value)
Cells(b, 23).Value = v

End If
Next b

End If
Next ws

End Sub

--
Regards,
Tom Ogilvy