Simple number manipulation by macro help

[ad9051]

Hello, firstly many thanks for taking the time to view my problem. I have to say i think this might be the simplest problem i have put on here.

Essentially all i am aiming to do is manipulate numbers in multiple datasets via a macro. I am aware that this could be done via formulae very easily, however due to the nature of the spreadsheet this is not the best option.

I have attatched a simple example spreadsheet to clarify the problem.

Again many thanks for your time.

Alan

[TMS]

It would be easy enough to determine the last row of data based on column C and then loop through column B until you go beyond the last row of data. You can also determine the start of each dataset based on column B and the depth of each dataset on column C ... width looks to be standard; is it?

Having determined the scope of each dataset, you can loop through each cell in the range applying the required change.

How do you determine what needs to be done to each dataset? Is each dataset updated in situ?

Regards

[ad9051]

Each dataset is updated in situ yes, i have had macros created for these datsets before they normally use code such as below to help identify the size of the datasets. (I think this is the corrct part of the code.)

All the best Alan

[Domski]

This should work although looping through all the cells in each data set wouldn't be necessary if I could get my head round how to use Evaluate properly.

Sub test()

Dim rngLoopRange As Range
Dim rngLoopRange2 As Range

For Each rngLoopRange In Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)

    If rngLoopRange <> "" Then
        With rngLoopRange.CurrentRegion
            For Each rngLoopRange2 In .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
                rngLoopRange2.Value = rngLoopRange2.Value * 2.5
            Next rngLoopRange2
        End With
    End If

Next rngLoopRange

End Sub

Will post back if can improve it at all.

Dom

[Domski]

Popping the value to mutliply by in an out of the way cell and using Paste Special...Multiply is probably more efficient:

Sub test()

Dim rngLoopRange As Range

With Range("IV1")
    .Value = 2.5
    .Copy
End With

For Each rngLoopRange In Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)

    If rngLoopRange <> "" Then
        With rngLoopRange.CurrentRegion
            With .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
                .PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
            End With
        End With
    End If

Next rngLoopRange

Range("IV1").ClearContents

End Sub

Dom

[ad9051]

Whilst both worked with the datasets i provided, in my real datasets i do not have them labeled in that way. the colomns to the left and right of the cells to be manipulated also contain data. Apolagies for prviding an unrepresentative dataset.

Cheers Alan

[Domski]

Try this:

Sub test()

Dim rngLoopRange As Range

With Range("IV1")
    .Value = 2.5
    .Copy
End With

For Each rngLoopRange In Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)

    If rngLoopRange <> "" And rngLoopRange.Offset(1, 0) = "" Then
        With rngLoopRange.CurrentRegion
            With .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 2)
                .PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
            End With
        End With
    End If

Next rngLoopRange

Range("IV1").ClearContents

End Sub

Dom

[ad9051]

hello dom, many thanks for the contribution this latest one doesnt seem to work?

Many thanks Alan

[Domski]

That's odd. Worked for me but had to move your comments in your example so they weren't in the column next to the data.

I'm on my phone and out this eve so will look tomorrow if still no good.

Dom

[Domski]

Have you got this working?

Dom