Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

How do I even start to explain this???

Well, I'm trying so hard to make loop macro to work but it's just not coming

out right.

Ok, I'm trying to edit a huge file that has many link formulas and

calculations that eats up a lot of megs. So I copy the formulas to an empty row

(row number 3) on top of the spreadsheet and value out the whole spreadsheet to

save time. So on the next update I would only calculate the rows that needs to

be changed using the formulas in row 3. This spreadsheet has 60,000 rows of

information with all kinds of vlookups, sumifs, ifstatements, if is errors and

so on. Sometimes I have hundreds of rows that needs to be updated. I was trying

to use a macro that would look for the "X" mark in the end of the column and

update the rows that applies and also value out the formulas after it has

updated the changes to minimize file space.

So is there a magic loop macro that would copy the formula that is in row 3

and paste formula in each rows has "x" mark in the end column of the report and

value them out in the end? Also the macro would automatically stop at the end.

> the spreadsheet and value out the whole spreadsheet to save

> time.

Do you mean delete as many cell contents as you can?

> was trying to use a macro that would look for the "X" mark in

> the end of the column

Do you mean an X in a separate column or an X actually at the end of the

contents of a cell like "2345 X"

> also value out the formulas after it has updated the changes

> to minimize file space.

&

> in the end column of the report and value them out in the

> end?

Again... What do you mean by value out please?

For row 3... Is there a blank cell at the end? That is, could that be a

signal to stop... Process from col 1 row 3 to col N row 3 till we get to a

blank cell??

For the vertical process... Does the data start and end at specific rows or

again... Can we stop if a cell is blank?

Here is what I *think* you mean...

You have...

A set of formulae in row 3.

Beneath each formula you have a set of "vertical" data.

In a separate column at the end of the sheet you have an "X"

You want to...

Loop around row 3 col 1 to row 3 col N till you get to a blank.

Pick up the formula in row 3.

Go down the end column for row 4 to M with the "X"s in it.

For each row with an X in replace the value of the cell at row M col N with

that formula.

For each column without an "X" delete what's in row M col N.

At the end zap the formulas in row 3.

Is that correct please?

Thanks for responding to my message. I appreciate your help so much.

I have responded in blue the questions that you had for me below.

Please review and let me know if I have clearly expressed my needs.

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

As always, thanks for your fast response. It is pretty impressive what you do.

I'll follow your instructions carefully and let you know if everything went

through ok.

Again, many thanks for your support and talk to you soon.

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

Ok... I'm working on it but a bit busy at the mo... It may be as late as

tommorow before I get back.

You're introducing a whole new language to me!!! "value out" and "flow".

Ok... I had a bit of time so I looked at the code again.

The following will I think do what you want.

I've made 2 changes.

1) Instead of pasting the formula in I've *copied* it from row 3. That means

the formula keeps the relative references and "flows". Hehehe.

2) I've left the copy and paste special till the end and done it for the

whole data area instead of one cell at a time.

If anything it should go a little faster now!

Sub subValueOut()

Dim rlCurrentRow3Cell As Range

Dim rlCurrentColumnCell As Range

Dim rlCurrentXCell As Range

Dim rlAllData 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)

' Copy cell in row 3 so that it "flows".

rlCurrentRow3Cell.Copy

rlCurrentColumnCell.Activate

ActiveSheet.Paste

DoEvents

Next llColumn

Else

' Skip this row if no X.

End If

Next llRow

' Done with formulae.

' Copy paste the whole data area.

Set rlAllData = Range( _

Cells(llDataStartRow, llDataStartColumn), _

Cells(llDataEndRow, llDataEndColumn) _

)

rlAllData.Copy

rlAllData.PasteSpecial _

Paste:=xlPasteValues, _

Operation:=xlNone, _

SkipBlanks:=False, _

Transpose:=False

MsgBox "Done."

End Sub

Unbelievable. It works perfectly!!!! You are amazing!!!!

You might not know how much this means to me and my department. We are all so

excited here at Disney Finance thanks to you.

I hope I can be even half as good as you someday. How do you guys at

YahooExcelVBA group do this? Do you guys take classes? Or you guys are self

taught. Either way what you guys do is very impressive. Thanks for just being

there for me and others who need help.

Again thank you so much and definitely I'll keep in touch with you.

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

Related Topics:

- Loop macro in Excel
- Macro's In Excel
- how can paste excel data in autocad command line using excel macros
- Add-Ins loaded into Excel
- looping in excel
- to loop or not to loop
- program using while loop and for loop
- Run Excel Macros in Online Office 365
- excel Macro Help
- Removing a vba module or macro with another macro
- Removing macro's shortcut key
- Excel Pivot Table Drop Down Macro
- Excel Macro
- hyperlink to a VBA macro, on an Excel sheet
- Recording a macro in EXCEL that will record mouse clicks
- Excel Macro Help Needed
- Excel Macros
- Why Excel refused to record my Macro?
- executing Excel Macros
- Excel Record Macro Examples
- Can we make PC to Phone calls using Macro Code in Excel VBA
- playing avi file in excel using macro
- Macro in Excel
- Running Excel VBA macros in OpenOffice Calc
- Can't open excel file after editing macro