Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

replace value in cell based on adjacent another cell



I  have  sheet  contains  items  in  column C ,G    what  I  want   replacing  items  for column C based on column G  . the  code  does  it,  but  the   problem  if  there  are items  in  column G  and  the  adjacent  cells in  column C are  empty  will  also  bring  new  items  from  column G  i  don't  want  it  . should  keep  empty for  adjacent cells in  column C .

Sub Demo1()
    Dim V, S%
        V = Sheets(4).UsedRange.Columns(7).Value2
        Sheets(4).UsedRange.Columns(3).Value2 = V
        End If
End Sub

would  appreciate  if  anybody  help .



Hi Tubrak

Sorry but I don't understand your question fully.

If there's a value in cell A1 of that sheet (and I ignore the stranded End If line), your code above will simply replace everything in column G with values from column C.(not the other way around, as your question states).

Are you saying you want to replace the value in G only if the cell in column G is blank?

Please clarify your question, preferably adding a file to save us time.
John_Ru (rep: 4312) Nov 15, '22 at 4:31 pm
yes  you're  right  about  error in the code with  comparison my  details .
I  edited  the  code  and  attach  file  with  what  i expect .
you  will  note  new  item  in  column G   should  not  copy  to  column C   ., should  not  fill blank cells in column C .
tubrak (rep: 18) Nov 15, '22 at 5:09 pm
Tubrak. Have answered from my phone- if not right, I will look again tomorrow (it's getting late here! ) 
John_Ru (rep: 4312) Nov 15, '22 at 5:42 pm
Add to Discussion


Selected Answer


To copy items from column F to (existing items in) C in your demo file, I suggest you use:

Sub Demo1()

With Sheets(4). Range("B1") .CurrentRegion

        .Value2=. Offset(0, 4).Value2

End With

End Sub

Hope this helps. 



Hi John 
thanks  for  this  solution .
tubrak (rep: 18) Nov 16, '22 at 2:27 am
Thanks for selecting my answer, Tubrak. 
John_Ru (rep: 4312) Nov 16, '22 at 2:43 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login