PDA

View Full Version : populating a cell with value from another



mattster1010
07-28-2008, 05:11 AM
Hi All,

I have two cells on an excel spreadsheet, one holds a numeric value the other holds a three letter word. I need a formula that will replace the contents of the numerical value if the three letter word cell is not null with the value of the three letter word cell, example below:

Cell 1 = 7 Cell 2 = ABS

Can anyone help?

Cheers,

Mattster

Bob Phillips
07-28-2008, 05:17 AM
You cannot have a formula and a value in a cell, unless the formula returns the value

=IF(C2<>"",C2,7)

marshybid
07-29-2008, 07:48 AM
Hi There,

the code below assumes that your cell 1 (numeric) is in column A and your cell 2 (three letter code) is in column B

You can change as per your requirement


Sub chngvalofcell()
Dim myBaseWorkSheet As Worksheet
Dim myBaseRange As range
Dim myBaseRow As range
Dim RowsCounter As Long
Set myBaseWorkSheet = ActiveWorkbook.ActiveSheet
Set myBaseRange = myBaseWorkSheet.Rows
For RowsCounter = myBaseRange.Rows.Count To 2 Step -1
Set myBaseRow = myBaseRange.Item(RowsCounter)
If Len(myBaseRow.Cells.Item(1, 1)) <> 0 Then

If myBaseRow.Cells.Item(1, 2) <> 0 Then

myBaseRow.Cells.Item(1, 1).Value = myBaseRow.Cells.Item(1, 2).Value

End If
End If
Next

End Sub


Hope this helps

Marshybid