VBA Use formula within all cells within a range (across numerous columns and rows), referencing row 5 in given column

[Moley84]

Hello all, This is my first post on MREXCEL.COM. Please be kind. I'm utterly stumped and rather need your help!

Here is a screenshot of some of my Excel sheet.

The range always starts in column H but the last column differs.
Row 5 will always be used for a count of how many visits the column is for - this is hand entered by the user
Row 8 is always the start of the pivoted data but the last row differs

I want to be able to say, where row 5 contains a value > 1 (that's important), then for all entries from row 8 and down within that column, divide the entry in row 8 and below by the value in row 5. Then iterate across all columns.

I have made a complete pig's ear of this and have spent too many hours puzzling over it.

 sourceSheet.Activate
 Dim lastrow As Long
 Dim lCol As Long
 Dim cell As Range
 Dim usedRange As Range
 Dim VisitCount() As Variant
 
 lastrow = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'I need to use row 5 as there are further columns to the right that don't need this calculation
 lCol = sourceSheet.Cells(5, sourceSheet.Columns.Count).End(xlToLeft).Column
 
 'In row 5, column H to last column
 VisitCount = Range("h5", Cells(lCol)).Value

 'Create the range of where the division needs to occur
 Set usedRange = Range("h8" & lCol & lastrow)
     
  ' Loop through each cell in the used range
    For Each cell In usedRange
        ' Check if the cell contains a value
        If cell.Value > 0 Then
        ' Within each column, divide the values in the used range with the value above in row 5.
        ' This next line is not working           
          usedRange.Replace What:=cell.Value, Replacement:=cell.Value * VisitCount
        End If
    Next cell

Any help will be much appreciated,
Moley84

 

[Vogateer]

Should everything just collapse to equal 1? That's what I'm getting when I try to test a portion of your data. Anyhow, here's the code I used to do it, but I think it could be simplified.
Make sure you change the sourceSheet below. I'm not sure whether you want it to do ActiveSheet or to reference a specific sheet.

Sub Moley84()

 Dim lastcolumn As Long, lastrow As Long
 Dim i As Long
 
 Dim DivisionRange As Range, DivisionArray As Variant
 Dim VisitCount As Long, VisitRange As Range
 Dim ChangedValue As Long
 Dim sourceSheet As Worksheet
 
 lastrow = Cells.Find(What:="*", _
                      LookIn:=xlValues, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious).Row

  Set sourceSheet = Sheets("Sheet4") ' CHANGE THIS!
 ' I need to use row 5 as there are further columns to the right that don't
 ' need this calculation
 lastcolumn = _
    sourceSheet.Cells(5, sourceSheet.Columns.Count).End(xlToLeft).Column
 
 'In row 5, column H to last column
 Set VisitRange = Range(Cells(5, 8), Cells(5, lastcolumn))
 Dim Visit As Range
 
 For Each Visit In VisitRange
    VisitCount = Visit.Value2
    ' Skip to next Visit if value is just 1
    If VisitCount = 1 Then
        GoTo next_visit
        End If
   
    Set DivisionRange = Range(Cells(8, Visit.Column), _
                              Cells(lastrow, Visit.Column))
    DivisionArray = DivisionRange.Value2
    For i = LBound(DivisionArray) To UBound(DivisionArray)
        If DivisionArray(i, 1) > 0 Then
            DivisionArray(i, 1) = DivisionArray(i, 1) / VisitCount
            End If
        Next i
    DivisionRange = DivisionArray
   
next_visit:
    Next Visit

End Sub

 

[Moley84]

Dear Vogateer,

Thank you so much for replying and helping me. I really do appreciate it. Yes, I need 1s in their place.

When I run the code, I am getting a Type Mismatch error for this line of code:

Does it (somehow) need to read from row 8 rather than "M0.5, M0.75?

Thank you.

 

[Vogateer]

I'm not sure why it's reading from row 7 instead of row 8, because I hard-coded 8 in the VBA.

Maybe try to change the 8 to a 9 in this code:

Set DivisionRange = Range(Cells(8, Visit.Column), _
                              Cells(lastrow, Visit.Column))

to

Set DivisionRange = Range(Cells(9, Visit.Column), _
                              Cells(lastrow, Visit.Column))

This is wild, because it's reading from row 8 on my example sheet I made.

 

[Moley84]

Thank you. It's working. It was due to further additional rows below in different formats.