Macro Help

[David Ryan]

Hi Folks
I with the help of Patrick Molloy, Kevin Smith and Joel from these groups
(thanks guys!) created a spreadsheet with a macro to move data from one row
to another. The sheet is locked so has to unlock etc prior to moving. Works
great however i need it to not move that data if the destination cells has
data already entered so should prompt for another destination. See attached
code.

Also to push the friendship
I would like the ability to move the data based in the data in the first
column e.g.
row 6 column A may have "1C" entered and row 8 column A May have 1B. Rather
than entering the row 6 in the input box from field the user enters what is
in cell 6A ie 1C etc

Again thanks in advance

Option Explicit
Sub MoveData()
Dim FROMrow As Long
Dim TOrow As Long

Dim c As Range
'To unprotect:
ActiveSheet.Unprotect Password:="pcc"
'To protect:
'ActiveSheet.Protect Password:="pcc"

On Error GoTo Canceled

FROMrow = InputBox("FROM row: ....", "MOVE FROM ")
TOrow = InputBox("TO row:...", "MOVE TO ...")

Cells(FROMrow + 5, "B").Resize(, 24).Copy
Cells(TOrow + 5, "B").PasteSpecial xlPasteAll
Application.CutCopyMode = False
Cells(FROMrow + 5, "B").Resize(, 24).ClearContents

'To protect:
ActiveSheet.Protect Password:="pcc"

ActiveSheet.Unprotect Password:="pcc"
Range("H6:H45").Select
For Each c In Selection
If c.Value = "" And _
c.Locked = True Then
c.Locked = False
End If
Next
Range("H1").Select
ActiveSheet.Protect Password:="pcc"

Canceled:
End Sub

 

[Patrick Molloy]

my first reply was to use cells to indicate the first and last rows,
In this query, you're asking that we use Range("A6") and Range("A8") which
have the values 1C and 1B
What does 1C and 1B mean?

 

[David Ryan]

Hi Patrick

It was just an example.

They will be along the lines of 21A, 21B, 21C, 22A, 22B ... down the column.
they are "location" references that stay constant. so the data will be moved
from row B including 24 cells along.

Cells(FROMrow??, "B").Resize(, 24).Copy

 

[Patrick Molloy]

I'm sorry. Maybe I'm a little slow on the uptake.
Your INPUT statement expects a number representing the row. You want to
replace these by a cell or a range or a column, but the how iis confusing me.
I don't understand what you mean by 21A, 21B down the column. How do I
interpret this? are those the cells that contain a number? remember I can't
see what tyou're lookign at re the sheet itself, so I'm lookng for clarity
here

thanks

 

[David Ryan]

Hi not slow

the spreadsheet is set out so that row 1 to 5 are headings etc

the data starts in row 6. column A has the location references and this is
locked and constant.
cell references
A6 = 21A
A7 = 21B
A8 = 21C
A9 = 21D
A10 = 22A
A11 = 22B etc

rather than using the row number to identify the data to be selected and
moved i would like to use the data in column A ie the location references,
users know these well. so in the input box currently a user needs to identify
that 21A is in row 6 and type 6 in the row from and the same for the row to.
it would be easier for them to just enter 21A and the code knows that that
relates to row 6 selects data from row 6 column b across for the number of
columns necessary currently 24 and move it to the required row again
identified by the location marker.

what you have provided works great, but it would be easier for the user to
use the location marker.

I appreciate your ongoing time and help Patrick. I hope my explanation is a
bit clearer.

 

[Patrick Molloy]

hmmm still fuzzy

so your user no lomger inputs a number, he input simething like 24B

the code needs to find where this is in column A, and that identifies which
row to copy from/to?


add
DIM FromInput As String
so after


FromInput = InputBox("FROM row: ....", "MOVE FROM ")

add

FromRow = match(FromInput ,Range("A1:A100").False)

similarly for the ToRow, adding ToInput as a string variable

hope this does it

 

[David Ryan]

So am I, however you are correct in what I want to do. You are talking to a
code moron so sorry for the hassel

i am sure i have done something wrong as the code does not work. i think too
many "DIMs". when run get error box with "Complie error: Sub or Function not
defined". and in the code window "Match" is highlighted.

new coding

Sub MoveData()
Dim FROMrow As Long
Dim TOrow As Long

Dim c As Range
'To unprotect:
ActiveSheet.Unprotect Password:="pcc"
'To protect:
'ActiveSheet.Protect Password:="pcc"

On Error GoTo Canceled

FROMrow = InputBox("FROM row: ....", "MOVE to # ...")
TOrow = InputBox("TO row: ....", "MOVE to #...")

Dim FromInput As String

FROMrow = Match(FromInput, Range("A6:A45").False)
Cells(FROMrow, "B").Resize(, 24).Copy
TOrow = Match(FromInput, Range("A6:A45").False)
Cells(TOrow, "B").PasteSpecial xlPasteAll
Application.CutCopyMode = False
Cells(FROMrow, "B").Resize(, 24).ClearContents

'To protect:
ActiveSheet.Protect Password:="pcc"

ActiveSheet.Unprotect Password:="pcc"
Range("H6:H45").Select
For Each c In Selection
If c.Value = "" And _
c.Locked = True Then
c.Locked = False
End If
Next
Range("H1").Select
ActiveSheet.Protect Password:="pcc"

Canceled:
End Sub