Copy and paste a value in a phrase

[Emily9093]

Hi All,

I am trying to copy and paste value from one cell to another. The value will be pasted after a phrase and in between quotation marks. For example:

Before copy & paste A1: 4567892300 C1: phone number = "" active etc.
After copy & paste A1: 4567892300 C1: phone number = "4567892300" active etc.

I know several ways to copy and paste in VBA, but is having trouble inserting value in a specific location.

Could anyone help me out on this?

Thanks!

Emily

 

[Momentman]

You want to add the value of A1 into C1 in between the double quotes in C1, is that it?

Just an idea

Sub GetRangeC1()
    ask = InStr(1, Range("C1"), """")
    Range("C1") = Mid(Range("C1"), 1, ask) & Range("A1") & Mid(Range("C1"), ask + 1, 100)
End Sub

 

[Emily9093]

You want to add the value of A1 into C1 in between the double quotes in C1, is that it?

Just an idea

Sub GetRangeC1()
    ask = InStr(1, Range("C1"), """")
    Range("C1") = Mid(Range("C1"), 1, ask) & Range("A1") & Mid(Range("C1"), ask + 1, 100)
End Sub

Thank you! That was very helpful. Is there a way to apply it to all values in two columns instead of just 2 cells?

 

[Momentman]

like this

Sub GetRangeC1()
    For I = 1 To Range("C" & Rows.Count).End(xlUp).Row
        ask = InStr(1, Range("C" & I), """")
        Range("C" & I) = Mid(Range("C" & I), 1, ask) & Range("A" & I) & Mid(Range("C" & I), ask + 1, 100)
    Next
End Sub

It will apply to all cells in column C, referencing column A

 

[Emily9093]

like this

Sub GetRangeC1()
    For I = 1 To Range("C" & Rows.Count).End(xlUp).Row
        ask = InStr(1, Range("C" & I), """")
        Range("C" & I) = Mid(Range("C" & I), 1, ask) & Range("A" & I) & Mid(Range("C" & I), ask + 1, 100)
    Next
End Sub

It will apply to all cells in column C, referencing column A

Thank you! It was flawless!

 

[Momentman]

Thank you! It was flawless!

Thanks for the kind words. you are welcome

 

[Emily9093]

Thanks for the kind words. you are welcome

Hi Momentman,

If I have multiple quotes within column C, and I would like to insert the same phone number from column A to all quotes in column C. How should I code for such scenario?
For example:
Before copy & paste A1: 4567892300 C1: phone number = "35000" active etc.
phone number repeated = "36000" active etc.
.............
After copy & paste A1: 4567892300 C1: phone number = "4567892300" active etc.
phone number repeated = "4567892300" active etc.
......

Here is the revised code I tailored towards the requirements of my project.

Sub GetRangeC1()
    For I = 2 To Range("U" & Rows.Count).End(xlUp).Row
        ask = InStr(1, Range("U" & I), """")
        Range("U" & I) = Mid(Range("U" & I), 1, ask) & Range("O" & I) & Mid(Range("U" & I), ask + 6, 100)
        If Range("O" & I) = "" Then
                    Range("U" & I) = Mid(Range("U" & I), 1, ask) & Range("O" & I - 1) & Mid(Range("U" & I), ask + 1, 100)
        End If
    Next
End Sub

Thank you for the help!

 

[Momentman]

Try this

Sub GetRan1geC1()
    For I = 1 To Range("C" & Rows.Count).End(xlUp).Row
        Range("C" & I).Replace What:="""*""", Replacement:="""" & Range("A1") & """", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next
End Sub

 

[Emily9093]

It worked! Thank you a bunch!