Please help - how do i insert a value to from a cell to another using macro.

[monmon]

Hi,

Please help me. I've the following data in Column E.

is it possible to use a macro such that once the macro detects that it is a blank cell, it will automatically combine the adjacent Column C cell combined with Column H cell to give a value?

Here is my data.

A	B	C	D	E	F	G	H
		tiger		mary LN			LN
		monkey					US
		dog		tom GB			GB

<TBODY>
</TBODY>

Here is my desired results. Note that cell E3 has become monkey US.

Please note that i would have a different list daily with varying amount of data.

Thanks for your help!

A	B	C	D	E	F	G	H
1		tiger		mary LN			LN
2		monkey		monkey US			US
3		dog		tom GB			GB

<TBODY>
</TBODY>

 

[John Davis]

Maybe:

Sub monmon()
Dim lr As Long
Dim rcell As Range

lr = Cells(Rows.Count, 3).End(xlUp).Row

For Each rcell In Range("E2:E" & lr)

    If rcell.Value = "" Then rcell.Value = rcell.Offset(, -2).Value & " " & rcell.Offset(, 3).Value
    
Next rcell

End Sub

 

[MickG]

Not Functional !!!!

 

[Trevor G]

Not tested but something like this may work

Sub combine1()
Dim Rng As Range
Set Rng = Range(Range("E1"), Range("E" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks)
Rng.Value = Rng.Offset(, -2).Value & " " & Rng.Offset(, 3)

End Sub

 

[John Davis]

Not Functional !!!!

What do you mean by Not Functional???

 

[monmon]

Hi all, thanks for your help.

Here's another question.

If some cells in Column H is blank. is it possible to just include data cell in C?

What I meant is in the event cell H2 is missing (for example), how can i revise the code to make it contain cell C2 value?

In addition, if Cell C is missing, can i use cell D2 combined with H2 instead?

 

[monmon]

Hi all, thanks for your help.

Here's another question.

If some cells in Column H is blank. is it possible to just include data cell in C?

What I meant is in the event cell H2 is missing (for example), how can i revise the code to make it contain cell C2 value?

In addition, if Cell C is missing, can i use cell D2 combined with H2 instead?

Anyone?

 

[John Davis]

Not sure I follow you, so test on a copy first, to see if this does what you want.

Sub monmon()
Dim lr As Long
Dim rcell As Range

lr = Cells(Rows.Count, 3).End(xlUp).Row

For Each rcell In Range("E2:E" & lr)

    If rcell.Offset(, -2).Value <> "" Then
        If rcell.Value = "" Then rcell.Value = rcell.Offset(, -2).Value & " " & rcell.Offset(, 3).Value
    ElseIf rcell.Offset(, -2).Value = "" Then
        If rcell.Value = "" Then rcell.Value = rcell.Offset(, -1).Value & " " & rcell.Offset(, 3).Value
    End If
    
Next rcell

End Sub

 

[monmon]

Not sure I follow you, so test on a copy first, to see if this does what you want.

Sub monmon()
Dim lr As Long
Dim rcell As Range

lr = Cells(Rows.Count, 3).End(xlUp).Row

For Each rcell In Range("E2:E" & lr)

    If rcell.Offset(, -2).Value <> "" Then
        If rcell.Value = "" Then rcell.Value = rcell.Offset(, -2).Value & " " & rcell.Offset(, 3).Value
    ElseIf rcell.Offset(, -2).Value = "" Then
        If rcell.Value = "" Then rcell.Value = rcell.Offset(, -1).Value & " " & rcell.Offset(, 3).Value
    End If
    
Next rcell

End Sub

Hi John, that's great! Thanks for help!

 

[John Davis]

You're welcome. Glad to help out.