PDA

View Full Version : Copy Cells with Range(Cells R,C). match returning location in numbers



jcramb
10-16-2017, 03:18 PM
I'm using a the match command to find a column and a row. Both are coming back as numbers.
Then i want to use the the row and column numbers to copy cell into a changing amount of cells below to another location in the workbook.
I get error on the bottom line...

Sub CALENDAR2()

Dim CALENDAR As String
Dim CALENDAR_COL As Integer
Dim CALENDAR_DATE_ROWNUM As Integer
Dim ROWCOUNT As Single
Dim DURATION_ORIGINAL As Integer
Dim START_EARLY As Date
Dim FINISH_EARLY As Date
Dim x As Range


ROWCOUNT = 2


CALENDAR = Sheets("Resource Assignments").Range("V" & ROWCOUNT).Value
START_EARLY = Sheets("Resource Assignments").Range("L" & ROWCOUNT).Value
FINISH_EARLY = Sheets("Resource Assignments").Range("M" & ROWCOUNT).Value
DURATION_ORIGINAL = Sheets("Resource Assignments").Range("Q" & ROWCOUNT).Value

'Find Calendar Column in Calendar List
CALENDAR_COL = WorksheetFunction.Match(CALENDAR, Sheets("Calendar List").Range("1:1"), 0)


'Find Calendar Start Date Row Number
CALENDAR_DATE_ROWNUM = WorksheetFunction.Match(CLng(CDate(START_EARLY)), Sheets("Calendar List").Columns(CALENDAR_COL), 0)

Sheets("Calendar List").Range(Cells(CALENDAR_DATE_ROWNUM, CALENDAR_COL), Cells(CALENDAR_DATE_ROWNUM + DURATION_ORIGINAL - 1, CALENDAR_COL)).Copy

Bob Phillips
10-17-2017, 02:36 AM
Is this any better


Sub CALENDAR2()

Dim CALENDAR As String
Dim CALENDAR_COL As Integer
Dim CALENDAR_DATE_ROWNUM As Integer
Dim ROWCOUNT As Single
Dim DURATION_ORIGINAL As Integer
Dim START_EARLY As Date
Dim FINISH_EARLY As Date
Dim x As Range

ROWCOUNT = 2

CALENDAR = Sheets("Resource Assignments").Range("V" & ROWCOUNT).Value
START_EARLY = Sheets("Resource Assignments").Range("L" & ROWCOUNT).Value
FINISH_EARLY = Sheets("Resource Assignments").Range("M" & ROWCOUNT).Value
DURATION_ORIGINAL = Sheets("Resource Assignments").Range("Q" & ROWCOUNT).Value

With Worksheets("Calendar List")

'Find Calendar Column in Calendar List
CALENDAR_COL = WorksheetFunction.Match(CALENDAR, .Range("1:1"), 0)

'Find Calendar Start Date Row Number
CALENDAR_DATE_ROWNUM = WorksheetFunction.Match(CLng(CDate(START_EARLY)), .Columns(CALENDAR_COL), 0)

.Range(.Cells(CALENDAR_DATE_ROWNUM, CALENDAR_COL), .Cells(CALENDAR_DATE_ROWNUM + DURATION_ORIGINAL - 1, CALENDAR_COL)).Copy
End With

jcramb
10-17-2017, 06:35 AM
Is this any better


Sub CALENDAR2()

Dim CALENDAR As String
Dim CALENDAR_COL As Integer
Dim CALENDAR_DATE_ROWNUM As Integer
Dim ROWCOUNT As Single
Dim DURATION_ORIGINAL As Integer
Dim START_EARLY As Date
Dim FINISH_EARLY As Date
Dim x As Range

ROWCOUNT = 2

CALENDAR = Sheets("Resource Assignments").Range("V" & ROWCOUNT).Value
START_EARLY = Sheets("Resource Assignments").Range("L" & ROWCOUNT).Value
FINISH_EARLY = Sheets("Resource Assignments").Range("M" & ROWCOUNT).Value
DURATION_ORIGINAL = Sheets("Resource Assignments").Range("Q" & ROWCOUNT).Value

With Worksheets("Calendar List")

'Find Calendar Column in Calendar List
CALENDAR_COL = WorksheetFunction.Match(CALENDAR, .Range("1:1"), 0)

'Find Calendar Start Date Row Number
CALENDAR_DATE_ROWNUM = WorksheetFunction.Match(CLng(CDate(START_EARLY)), .Columns(CALENDAR_COL), 0)

.Range(.Cells(CALENDAR_DATE_ROWNUM, CALENDAR_COL), .Cells(CALENDAR_DATE_ROWNUM + DURATION_ORIGINAL - 1, CALENDAR_COL)).Copy
End With

jcramb
10-17-2017, 07:49 AM
Is this any better


Sub CALENDAR2()

Dim CALENDAR As String
Dim CALENDAR_COL As Integer
Dim CALENDAR_DATE_ROWNUM As Integer
Dim ROWCOUNT As Single
Dim DURATION_ORIGINAL As Integer
Dim START_EARLY As Date
Dim FINISH_EARLY As Date
Dim x As Range

ROWCOUNT = 2

CALENDAR = Sheets("Resource Assignments").Range("V" & ROWCOUNT).Value
START_EARLY = Sheets("Resource Assignments").Range("L" & ROWCOUNT).Value
FINISH_EARLY = Sheets("Resource Assignments").Range("M" & ROWCOUNT).Value
DURATION_ORIGINAL = Sheets("Resource Assignments").Range("Q" & ROWCOUNT).Value

With Worksheets("Calendar List")

'Find Calendar Column in Calendar List
CALENDAR_COL = WorksheetFunction.Match(CALENDAR, .Range("1:1"), 0)

'Find Calendar Start Date Row Number
CALENDAR_DATE_ROWNUM = WorksheetFunction.Match(CLng(CDate(START_EARLY)), .Columns(CALENDAR_COL), 0)

.Range(.Cells(CALENDAR_DATE_ROWNUM, CALENDAR_COL), .Cells(CALENDAR_DATE_ROWNUM + DURATION_ORIGINAL - 1, CALENDAR_COL)).Copy
End With

jcramb
10-17-2017, 07:56 AM
These two lines of code are working for me. I can locate the column and row of the cell in the table i am looking for.


'Find Calendar Column in Calendar ListCALENDAR_COL = WorksheetFunction.Match(CALENDAR, Sheets("Calendar List").Range("2:2"), 0)


'Find Calendar Start Date Row Number
CALENDAR_DATE_ROWNUM = WorksheetFunction.Match(CALENDAR, Sheets("Calendar List").Columns(CALENDAR_COL), 0)

This is the line of code that is not working. I want to copy this cell and a changing number of cells below to a new location. The number of cell to copy is defined by DURATION_ORIGINAL. I might be thinking about this wrong. Is there a quick way to convert Cell(R,C) to Range(A1)?

.Range(.Cells(CALENDAR_DATE_ROWNUM, CALENDAR_COL), .Cells(CALENDAR_DATE_ROWNUM + DURATION_ORIGINAL - 1, CALENDAR_COL)).Copy