Moving a cell two colums to the right

Thecraftycarrot

New Member
Joined
Nov 8, 2018
Messages
27
Hi All, i am trying to copy a cell to columns to the right.

The below code is relating to an if yes statement, that if criteria is met, it will find the the required cell and update it (successfully works)

Code:
Dim checkCell As Range
  Dim cell As Range
            Dim endRow As Long
            Dim updateRow As Long
            Dim updateCol As Long
            Dim colName As String
            Dim updateValue As String

Set checkCell = Sheets("Sheet 1 ").Range("G45")

If checkCell.Value = "Yes" Then

rowName = Sheets("Sheet1").Range("M45").Value
updateRow = Sheets("sheet2").Range("A4:a1000").Find(rowName, LookIn:=xlValues).Row
colName = "Persons name"
updateCol = Sheets("sheet2").Range("A3:AZ3").Find(colName, LookIn:=xlValues).Column
updateValue = Sheets("Sheet1").Range("E6").Value
Sheets("sheet2").Cells(updateRow, updateCol).Value = updateValue

However i need to tweek the above formula, instead of the updating the found cell i.e. Sheets("sheet2").Cells(updateRow, updateCol).Value - i need to copy and paste/offset this into a cell on the same row but two columns to the right.

i would imagine the code for updateValue will need to be something like this (but really have no idea how to get this to work):

Code:
 updateValue = Sheets("sheet2").Cells(updateRow, updateCol).Value.offset(0,2)
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
aint it a simple matter of .copy and then .offset(0.2) ?
* i aint the best in macro's but i have a code for my own sheet where i have to copy and paste things with offset through loops so i was thinking if its just a simple matter of .copy and .paste but with offset like i have in my sheet?
 
Upvote 0
i am not sure, never used vba for copy / paste offset etc. could you maybe respond with how you would expect it to work? i.e. a response with some coding. i dont need it to loop though, just copying that one cell.
 
Upvote 0
aha! just got it dont worry - code i used was this:
updateValue = Sheets("sheet2").Cells(updateRow, updateCol).Value

Sheets("sheet2").Cells(updateRow, updateCol).offset(0,2).Value = updateValue
 
Upvote 0
okay i hope it works,
replace Sheets("sheet2").Cells(updateRow, updateCol).Value = updateValue then with:
Code:
[COLOR=#333333]Sheets("sheet2").Cells(updateRow, updateCol).Copy
[/COLOR][COLOR=#333333]Sheets("sheet2").Cells(updateRow, updateCol).Offset(0.2)
[/COLOR].PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Make sure you use it in a different workbook because u cant undo changes from macro's. Some people forget this and mess up their sheets.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top