We help IT Professionals succeed at work.

How do I do relative cell reference in an Excell macro?

jhartski
jhartski asked
on
I have an EXCEL spreadsheet with the following data:
Column A = employee number
Column C = regular hours
Column D = vacation hours
Column F = holiday hours

The number of records in the sheet can vary from week to week.  What I want to do is create a new spreadsheet, in the same workbook  in the following format:
Column A = employee number
Column B = hours
Column C = an earnings code (REG, VAC, HOL) depending on the type of hours

The new spreadsheet would have 3 lines for every employee.  Some of the hours will be zero but that is OK.  I can deal with that later.
Emp1  hours  REG
Emp1  hours  VAC
Emp1  hours  HOL
Emp2  hours  REG
Emp2  hours  VAC
Emp2  hours  HOL
etc, etc

I am trying to set up a DO LOOP to move the data from the original sheet to the new sheet, but I don?t know how to reference cell addresses.  I can use

range("A1").Select

to select my starting cell on the original sheet, but I don?t know how to use relative cell reference to select the next cell.  How do I move from cell to cell in my DO LOOP?

Comment
Watch Question

To find out how many rows you have: (suppose one of your columns is B.

in VBA run a simple test sub:
Sub Test()
 Range("B1").Select
 Range(Selection, Selection.End(xlDown)).Select
 Msgbox  Selection.End(xlDown).address
End Sub


The last line will give youthe addtess of your last row

Author

Commented:
Let me clarify my problem.  I don't need to know how many rows of data I have to move.  I need to know how to reference the relative cell address.
You can split the cell pointer to use "A" & intCounter.  Incriment that as needed.  I'm not real sure what it is exactly you are trying to do.  Do you always start at A1?

Author

Commented:
Following is the code I would use if I was to hard code the cell addresses for one employee.  I want to be able to loop through this code for however many records I have.  So the question is, how do I plug the cell address?  Do I use the RANGE command, or should I be using something else?  Yes, I will always start in A1 on the input and the output sheets.

? Create a row for the REGULAR hours
? Copy the employee number
    Sheets("Input").Select
    range("A1").Select
    Selection.Copy
    Sheets("Output").Select
    range("A1").Select
    ActiveSheet.Paste
? Copy the reg hours
    Sheets("Input").Select
    range("C1").Select
    Selection.Copy
    Sheets("Output").Select
    range("B1").Select
    ActiveSheet.Paste
? Plug the earnings code
    range("C1").Select
    ActiveCell.Formula = "REG"

? Create a row for the VACATION hours
? Copy the employee number
    Sheets("Input").Select
    range("A1").Select
    Selection.Copy
    Sheets("Output").Select
    range("A2").Select
    ActiveSheet.Paste
? Copy the vacation hours
    Sheets("Input").Select
    range("D1").Select
    Selection.Copy
    Sheets("Output").Select
    range("B2").Select
    ActiveSheet.Paste
? Plug the earnings code
    range("C2").Select
    ActiveCell.Formula = "VAC"

? Create a row for the HOLIDAY hours
? Copy the employee number
    Sheets("Input").Select
    range("A1").Select
    Selection.Copy
    Sheets("Output").Select
    range("A3").Select
    ActiveSheet.Paste
? Copy the holiday hours
    Sheets("Input").Select
    range("F1").Select
    Selection.Copy
    Sheets("Output").Select
    range("B3").Select
    ActiveSheet.Paste
? Plug the earnings code
    range("C3").Select
    ActiveCell.Formula = "HOL"



Author

Commented:
mmcmillen
I think I have this working.  Will confirm after a little more testing.  Thanks.
With my help?

Author

Commented:
mmcmillen,
Yes with your help.  What I did was set up a counter and used the following commands to reference the cell.

    counter = counter + 1
    range("A" & counter).Select
Richie_SimonettiIT Operations
CERTIFIED EXPERT

Commented:
Hearing...

Explore More ContentExplore courses, solutions, and other research materials related to this topic.