Try this. Create a new workbook for this purpose and make sure there are two worksheets in it. Press
Alt-F11 to open Microsoft Visual Basic, press
Ctrl-R to view the Project Explorer, then go
Insert >
Module. A new 'standard' code module will appear under
Modules, probably called
Module1. You might need to click the + symbol against
Modules to expand the group.
Double-click the name of this new module to open it. Remove any code you find in the code window (probably only the words
Option Explicit, if anything) and paste my code in its place - everything from
Option Explicit down to and including
End Sub:-
Code:
[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT]
[FONT=Fixedsys]Public Sub MoveValues()[/FONT]
[FONT=Fixedsys][/FONT]
[FONT=Fixedsys] Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim iLastRow As Long
Dim iRow As Long
Dim rTarget As Range
Set ws1 = ThisWorkbook.Sheets(1)
Set ws2 = ThisWorkbook.Sheets(2)
iLastRow = Application.WorksheetFunction.Max( _
ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row, _
ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row)
For iRow = 1 To iLastRow
Select Case True
Case IsEmpty(ws1.Cells(iRow, "A")) And IsEmpty(ws1.Cells(iRow, "B"))
' no data - ignore this row
Case IsEmpty(ws1.Cells(iRow, "A")) And Not IsEmpty(ws1.Cells(iRow, "B"))
ws1.Cells(iRow, "C") = "No value to copy"
Case Not IsEmpty(ws1.Cells(iRow, "A")) And IsEmpty(ws1.Cells(iRow, "B"))
ws1.Cells(iRow, "C") = "Target cell address missing"
Case Else
Set rTarget = Nothing
On Error Resume Next
Set rTarget = ws2.Range(ws1.Cells(iRow, "B").Value)
On Error GoTo 0
If Not rTarget Is Nothing Then
ws1.Cells(iRow, "A").Copy Destination:=rTarget
ws1.Range("A" & iRow & ":C" & iRow).ClearContents
Else
ws1.Cells(iRow, "C") = "Invalid target cell address"
End If
End Select
Next iRow
End Sub
[/FONT]
Return to your worksheet and make sure you're in
Sheet1. Click
Developer >
Insert (the toolbox with the crossed hammer & spanner), and select the first
Form control (tooltip "Button" if you mouse over it). Click-drag-release to place it on your worksheet - don't worry too much about size, shape and location as you can modify these later), then select the
MoveValues macro from the list and click
OK. There will be a grey 'design mode' frame around the button: click out of the button on to the worksheet to make this disappear.
Still in
Sheet1, enter some values in column A and some cell addresses in column B. Include one value with no cell address, one value with an invalid cell address and a cell address with no value. Click the button.
One of three things will happens:-
- Nothing at all
- VBA reports an error
- The values in the valid rows are copied on to Sheet2 and the ones on the invalid rows show an error message
Come back and tell me which.
