how do I write a macro to replace certain cells in a colum depending on other cell va

[bsw2112]

Hello,

I am very new to spreadsheets...

I was wondering if anyone can help me with writing a macro
that would replace cell values in a column based a values in certain cells in that column.

//i would like to replace the values of cells that have the value 0 in them with the value of a variable.

<pseudocode>

Sub ChangeAll()

dim intValueOfCell
Range("A1").Select
intValueOfCell= ActiveCell.Value

Do While ActiveCell.Value <> "" 'cell is empty

If ActiveCell.Value = 0 Then
ActiveCell.Value = intValueOfCell
Else
intValueOfCell = ActiveCell.Value
End If

'select next cell in column (don't know what is the code for that

' maybe ****************

Selection.Offset(0,1).Select

'**********************
Loop

Range("A1").Select
End Sub
</pseudocode>

example of a column

***column 1***

12
0
0
0
17
0
0
89
0
0
0
0
102
0
0
160

etc...
************

I would do this manualy but there are over 8000 records (cells)
with values in them

Please help

bsw

[calgal]

It worked for my small test sheet. You can put it in a module or the code for the worksheet itself. Either way it should work.

just find the last cell in your sheet and this will do the rest for you.
and replace "" with your variable.


Sub VoidZeros()
Dim x As Integer
Dim rng As Range

Set rng = Range("A1", "C55") 'c55 is an arbitrary cell I chose
x = rng.Rows.Count

For x = 1 To x
If Sheet1.Cells(x, 1) = 0 Then
Sheet1.Cells(x, 1) = ""
End If
Next
End Sub

[bsw2112]

thanks for your help calgal
i will try your example
i didn't know about Sheet1.Cells(x, 1)
i need to get some VBA tutorials.

bsw

[alex_read]

//i would like to replace the values of cells that have the value 0 in them with the value of a variable.

This sample will do the same as the above posted code, though it uses the usedrange object from Excel - you can call on this to find the last row, column or cell in the spreadsheet which contains a value in it...

Private Sub CommandButton1_Click()
    Dim intLoopCounter As Integer
    Const strMyVariableName As String = "A Value"
    
    For intLoopCounter = 1 To UsedRange.Rows.Count
        If Cells(intLoopCounter, 1) = 0 Then
            Cells(intLoopCounter, 1) = strMyVariableName
        End If
    Next intLoopCounter
End Sub

For the second part, the best advice I can give here would probably be to look at the MsPress books for office. There aren't any vba tutorial sites which I've found to be fantastic & the Microsoft written books on office are probably the best I've seen so far...

[bsw2112]

thanks alex_read

bsw

[Matt_T_hat]

Originally posted by bsw2112
I am very new to spreadsheets...

//i would like to replace the values of cells that have the value 0 in them with the value of a variable.

I would use a cell function =IF(C2<>0,C2,$A$1) where A1 is the value that replaces 0's and the colum you use for maths etc is calculated fromt eh typed info column next to it.

Never use VBa when the function is built in to the package already. Otherwise a search and replace would do the trick.

BTW: IMHO: Sounds like this speadsheet is in dire need of conversion to Access.