Formula to Text based on condition "if cell = 1"

[morenac]

Hello all,

I struggle with one thing, I would need to divide all figures in particular range of row by 1000 and then the formulas paste as text, if first cell will contain "1".

It means, table starts in row 21, range G21:G999 will contain "1" or "0", and range H21:AS999 contains formulas.
I would need to divide all the cells by 1000 and then paste all cells in row H21:AS21 as text IF cell G21 contain "1".
If G21 will be "0", it will do nothing and go to next line and repeat.

Could please someone help with writing this code? Unfortunately this is too much for me, I would be really really grateful!

Thank you all!

 

[Joe4]

What do these formulas in cells H21:AS999 look like?
Do they all return numbers?
Are any dependent on other formulas within that same range?
When you say "paste formulas as text", do you really mean post the hard-coded value instead of the formula?

 

[morenac]

Hello Joe4,

I am sorry, if I wrote it unclear. Actually H21:AS999 are mostly only links to other sheets, all of them are numbers - these would be marked with "1" in column G. For all these lines I would need just to replace formulas with its hard-coded value.

But some of the rows of H21:AS999 contains SUM formula for few cells above - these would be marked with "0" in column G and these lines should keep the SUM formula.
I thought that the 1 and 0 in G column would be best way how to recognize which line should be hard-coded and which should not be touched, because the line quantity will change, so I can not specify exact range of the replacing fomula by hard number.

So in practice there is for example
H21:H23 link to another sheet,
H24 is SUM(H21:H24),
then H25:H30 with link to another sheet
H31 is SUM(H25:H30) and so on for each column untill AS999

Thank you very much for your effort!

 

[Joe4]

Try this:

Sub MyCopyMacro()

    Dim myRow As Long
    Dim myCol As Long
    Dim myRange As Range
    
    Application.ScreenUpdating = False
    
'   Store 1000 in some unused cell
    Range("AZ1") = 1000
    
'   Loop through rows 21-999
    For myRow = 21 To 999
'       Check to see if column G equals 1
        If Cells(myRow, "G") = 1 Then
'           Set range to apply to
            Set myRange = Range("H" & myRow & ":AS" & myRow)
'           Hardcode range to value
            myRange.Value = myRange.Value
'           Divide range by 1000
            Range("AZ1").Copy
            myRange.PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks _
                :=False, Transpose:=False
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[morenac]

Joe4, you are amazing! It does exactly what I needed.

Thank you very much for your help!

 

[Joe4]

You are welcome!