Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Gertrude Miller   on Dec 11 In MS Office Category.

  
Question Answered By: Sairish Kauser   on Dec 11

So just to be clear....

Is what you want....
Go down a column with Xs in certain cells.
If there is an X in a cell loop  *across* that row  and replace what is in the
cells on that row with whatever formula is in row 3.
After the value has been calculated, replace the formula with the resultant
value.

Is that correct??

... And just to get you going.. The following code will ...
Go down column 6 looking for Xs from row 4 to 15.
When it gets an X go across that row from column 1 to 5.
Pick up the formula from row 3 for that column.
Put it in that row/column.
Copy the cell and paste special it back.

Be aware that the start  end values are all hard  coded.
Given a bit more time  I'd collect all the row 3 formulae in an array so that
I didn't have to look them up all the time.

Sub subValueOut()

Dim rlCurrentRow3Cell As Range
Dim rlCurrentColumnCell As Range
Dim rlCurrentXCell As Range
Dim llColumn As Long
Dim llRow As Long
Dim slFormula As String
Dim llColumnWithXes As Long
Dim llDataStartRow As Long
Dim llDataEndRow As Long
Dim llDataStartColumn As Long
Dim llDataEndColumn As Long

' Set initial values.
slFormula = ""
llColumnWithXes = 6
llDataStartRow = 4
llDataEndRow = 15
llDataEndColumn = 5
llDataStartColumn = 1

' Go DOWN the X column.
For llRow = llDataStartRow To llDataEndRow

Set rlCurrentXCell = Cells(llRow, llColumnWithXes)
If UCase(rlCurrentXCell.Value) = "X" Then

' Got an X.
' Go ACROSS.
For llColumn = llDataStartColumn To llDataEndColumn

Set rlCurrentColumnCell = Cells(llRow, llColumn)
Set rlCurrentRow3Cell = Cells(3, llColumn)

slFormula = rlCurrentRow3Cell.Formula

' Replace with formula from Row 3.
rlCurrentColumnCell.Select
rlCurrentColumnCell.Formula = slFormula

DoEvents

' Copy and pastes special.
rlCurrentColumnCell.Copy
rlCurrentColumnCell.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Next llColumn

Else

' Skip this row if no X.

End If
Next llRow
MsgBox "Done."
End Sub

Share: 

 

This Question has 7 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Loop macro in Excel Or get search suggestion and latest updates.


Tagged: