Macro to edit cell values and copy/paste

MickeyMB

New Member
Joined
Jul 12, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi all,
Thank you in advance. I'm very new to VBA/macros and I think I have a relatively easy task to complete, but I can't seem to figure it out. I received a bunch of UTMs for salmon redds, but in the wrong format and need to use a batch converter to change them to Lat/Longs in order to import the data to GIS. The batch converter also requires values to be in the same cell. So, I'm trying to use a macro to add the missing info to the UTMs and format them in to single cells to match the converter's requirements. Ex: If, A8= 536371 & B8 = 4888161, it needs to be changed so that A8 = 10N 536371mE 4888161mN. All cells will have 10N ******mE ******mN. The macro that currently works is as follows, but it's only filling in the original cell values, instead of adding the the missing formatting to each *unique* value set...

Sub reddtest()
'
' reddtest Macro
'
' Keyboard Shortcut: Ctrl+r
'
ActiveCell.FormulaR1C1 = "10N 536165mE"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "4888137mN"
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "10N 536165mE 4888137mN"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Does it have to be a macro? Couldn't you accomplish the same thing with the following formula if you inserted a column to the left of column A like this?

Excel Formula:
="10N "& B8 & "mE " & C8 &"mN"
10N 536371mE 4888161mN
536371​
4888161​
10N 536372mE 4888162mN
536372​
4888162​
10N 536373mE 4888163mN
536373​
4888163​
10N 536374mE 4888164mN
536374​
4888164​
10N 536375mE 4888165mN
536375​
4888165​
10N 536376mE 4888166mN
536376​
4888166​
10N 536377mE 4888167mN
536377​
4888167​
10N 536378mE 4888168mN
536378​
4888168​
10N 536379mE 4888169mN
536379​
4888169​
10N 536380mE 4888170mN
536380​
4888170​
10N 536381mE 4888171mN
536381​
4888171​
 

Forum statistics

Threads
1,148,147
Messages
5,745,055
Members
423,917
Latest member
Frank1931

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
Top