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: Laura Rodriguez   on Dec 11

WOW!!! It's pretty exciting to see your macro  coming alive in Excel. It is
exactly what I was looking for.

However, is it possible if you can help make one adjustment witht he macro?

When the macro replaces the row  that has an X with the formulas  in row 3 (in
current case row 5), the formula doesn't flow instead it pastes the same source
in row 5.

Example:

When I drag dow a formula from cell A5 to cell A6 below the formula adjust to
the conditions of cell A6. So when a formula in A5 is = B5+C5 and it is dragged
to cell A6 it changes to =B6+C6 and so on. I would like to have this function
applied in my macro.

Right now when I run the macro it doesn't adjust to the following cell instead
it's looked to cell A5. So any cell down the row A6, A7, A9, A12, etc, have the
same formula from as cell A5. Can you please help me adjust this macro so when
it is replaced by the formula down the rows  it adjusts accordingly?

Hey I can thank you enough for your help.

Here is how the current macro loolks for your reference.

Sub UltimatesMacro()

Dim rlCurrentRow10Cell 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 = 1
llDataStartRow = 10
llDataEndRow = 1500
llDataEndColumn = 84
llDataStartColumn = 2

' 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 rlCurrentRow10Cell = Cells(10, llColumn)
slFormula = rlCurrentRow10Cell.Formula

' Replace with formula from Row 10.
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."

Range("A1").Select
Sheets("NU").Select
Application.CutCopyMode = False

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: