VBA - Cell address & offset columns using variable

Hi. I have a long list of zip codes. The zip codes have to be pasted to different sheets, 48 at a time, two columns per sheet. Because this routine is going to be used by someone even less savvy in VBA than I am it needs to be simple, if inefficent, code. Here's what I've got so far. Works fine.

Application.Goto Reference:="ZipStart"
ActiveCell.Range("A1:C48").Select
Selection.Copy
ActiveCell.Offset(48, 0).Range("A1").Select
ActiveWorkbook.Names.Add Name:="NewStart", RefersTo:=Selection
Application.Goto Reference:="Paste1A"
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.Goto Reference:="NewStart"
ActiveCell.Range("A1:C48").Select
Selection.Copy
ActiveCell.Offset(48, 0).Range("A1").Select
ActiveWorkbook.Names.Add Name:="NewStart", RefersTo:=Selection
Application.Goto Reference:="Paste1B"
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

What I'd like to do is have a cell in the spreadsheet where the number of zips that will fit on a page will be indicated (because of formatting, etc. this might vary by workbook). Then, use the number from that cell in the VBA for the 48 in ("A1:C48") and the Offset(48, 0).

I've tried adding a range called "ZipsPerPage" to the spreadsheet. Cell value is 48. Setting a VBA variable of ZipsPerPage and making it equal to the range. I've tried dimming it as an integer & string and inserting it into the code in place of the 48's but can't get it to work. Any ideas?

Thanks!


Hi,
Is there anyway to obtain the value of the last nonblank cell in a column using a macro?

Thanks.

Hello All,

I've been looking through the VBA help to find the answer to this with no
luck.

Can I assign the Row number and Cell number for the currently sellected cell
to individual variables for use later in formulas and range selection. For
example, I have a large list. I move to the end of the list by holding down
[Ctrl] and then the down arrow. This is where I would like to store the row
number into a variable. I would then move to a predetermined column in that
row and enter formulas using these variables.

If I can assign these to variables, do I have to be concerned with the cell
address format I use in my formulas or can these variables automaticaly
converted to the different address formats?

Thanks for any help anyone can provide,

Conan

Is it at all possible to SUM using variables in the range?

I know you can get the location of a cell into a variable form by:

Hi Guys,

Hoping someone can shed some light on where I am going wrong here.

I am trying to automate parts of a break rostering system.

The sub is supposed to check through a range which holds each persons
availability, assigned breaks, and other information which would not be
made available to everyone.

When it finds a cell formatted to represent a break it will look up the
person's name and the break time.

It will then search through a second range which is the simple version
of the roster which is sent out to each person.

It searches for the relevant time in this second range to get the cell
address. It then should cycle down each row underneath the target time
until it finds an empty cell where it can then put the person's name.

The part where I am slipping up is "cycle down each row underneath the
target time until it finds an empty cell"

Can someone please look at the following code and advise if there is an
obvious error?

I am quite noob(ish) when it comes to this so feel free to laugh and
point (and provide advice) if there is an easier way of doing this

TIA

Deon.

Private Sub Generate_Roster()
Dim rngTodaysResources, rngTodaysRoster As Range
Dim index, rowCounter, colBreakTime, rowBreakTime, rowBreakName,
colBreakName, colRosterTime, rowRosterTime As Integer

Set rngTodaysResources = Range("A1:AT45")
Set rngTodaysRoster = Range("AV24:BB62")

rowCounter = 0

For Each Cell In rngTodaysResources
If Cell.Interior.Color = vbBlue And Cell.Value = "" Then

colBreakTime = Cell.Column
rowBreakTime = 1
colBreakName = 1
rowBreakName = Cell.Row
targetBreakTime = Cells(rowBreakTime, colBreakTime)
targetName = Cells(rowBreakName, colBreakName)

'' The following returns expected values which means that the
last block of assignments worked
MsgBox targetName & " at " & Format(targetBreakTime, "hh:mm AMPM")

'' Cycle through today's roster to find where the matching time is
in the roster
For index = 1 To rngTodaysRoster.Cells.Count
If rngTodaysRoster.Cells(index).Value = targetBreakTime Then
colRosterTime = rngTodaysRoster.Cells(index).Column
rowRosterTime = rngTodaysRoster.Cells(index).Row

'' This next MsgBox returns expected values, the one after that
does not however.
MsgBox "Match found at " &
rngTodaysRoster.Cells(index).Address & ", Should match row " &
rowRosterTime & " and column " & colRosterTime

'' This next MsgBox returns the right values for rowCounter,
but not for the address,
'' doesn't matter if I use -
'' rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0)
'' or
'' rngTodaysRoster.Cells((rowRosterTime + rowCounter),
colRosterTime)
'' It always states an address which is not anywhere near
correct, usually CQ58 or so.

MsgBox "RowCounter is " & rowCounter & " entering
search at " & rngTodaysRoster.Cells((rowRosterTime + rowCounter),
colRosterTime).Address

'' Never enters this loop. Address issue above.
While rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0).Value ""
rowCounter = rowCounter + 1
MsgBox rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0).Address
MsgBox "Value of cell is :" &
rngTodaysRoster.Cells(rowRosterTime, colRosterTime).Offset(rowCounter,
0).Value & ":"
MsgBox "No match found at row " & (rowRosterTime +
rowCounter) & " and column " & colRosterTime
Wend

rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0) = targetName

MsgBox "RowCounter is " & rowCounter & " Exit search at
" & rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0).Address
End If
Next
End If
Next

End Sub

Hi Guys,

Hoping someone can shed some light on where I am going wrong here.

I am trying to automate parts of a break rostering system.

The sub is supposed to check through a range which holds each persons
availability, assigned breaks, and other information which would not be
made available to everyone.

When it finds a cell formatted to represent a break it will look up the
person's name and the break time.

It will then search through a second range which is the simple version
of the roster which is sent out to each person.

It searches for the relevant time in this second range to get the cell
address. It then should cycle down each row underneath the target time
until it finds an empty cell where it can then put the person's name.

The part where I am slipping up is "cycle down each row underneath the
target time until it finds an empty cell"

Can someone please look at the following code and advise if there is an
obvious error?

I am quite noob(ish) when it comes to this so feel free to laugh and
point (and provide advice) if there is an easier way of doing this

TIA

Deon.

Private Sub Generate_Roster()
Dim rngTodaysResources, rngTodaysRoster As Range
Dim index, rowCounter, colBreakTime, rowBreakTime, rowBreakName,
colBreakName, colRosterTime, rowRosterTime As Integer

Set rngTodaysResources = Range("A1:AT45")
Set rngTodaysRoster = Range("AV24:BB62")

rowCounter = 0

For Each Cell In rngTodaysResources
If Cell.Interior.Color = vbBlue And Cell.Value = "" Then

colBreakTime = Cell.Column
rowBreakTime = 1
colBreakName = 1
rowBreakName = Cell.Row
targetBreakTime = Cells(rowBreakTime, colBreakTime)
targetName = Cells(rowBreakName, colBreakName)

'' The following returns expected values which means that the
last block of assignments worked
MsgBox targetName & " at " & Format(targetBreakTime, "hh:mm AMPM")

'' Cycle through today's roster to find where the matching time is
in the roster
For index = 1 To rngTodaysRoster.Cells.Count
If rngTodaysRoster.Cells(index).Value = targetBreakTime Then
colRosterTime = rngTodaysRoster.Cells(index).Column
rowRosterTime = rngTodaysRoster.Cells(index).Row

'' This next MsgBox returns expected values, the one after that
does not however.
MsgBox "Match found at " &
rngTodaysRoster.Cells(index).Address & ", Should match row " &
rowRosterTime & " and column " & colRosterTime

'' This next MsgBox returns the right values for rowCounter,
but not for the address,
'' doesn't matter if I use -
'' rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0)
'' or
'' rngTodaysRoster.Cells((rowRosterTime + rowCounter),
colRosterTime)
'' It always states an address which is not anywhere near
correct, usually CQ58 or so.

MsgBox "RowCounter is " & rowCounter & " entering
search at " & rngTodaysRoster.Cells((rowRosterTime + rowCounter),
colRosterTime).Address

'' Never enters this loop. Address issue above.
While rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0).Value <> ""
rowCounter = rowCounter + 1
MsgBox rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0).Address
MsgBox "Value of cell is :" &
rngTodaysRoster.Cells(rowRosterTime, colRosterTime).Offset(rowCounter,
0).Value & ":"
MsgBox "No match found at row " & (rowRosterTime +
rowCounter) & " and column " & colRosterTime
Wend

rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0) = targetName

MsgBox "RowCounter is " & rowCounter & " Exit search at
" & rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0).Address
End If
Next
End If
Next

End Sub

Hi,

How to sum a variable no of cells in a column using VBA, I want the code to
sum up the cells on a certain condition of other cells such as IF(D10=""),
then add up all cells between N1 and N9, and so on IF D16="", then add up all
cells from N11 up to N15.
Can anybody help?
Regards.

I have 2 Sheets, "Template" and "Notes".

In the Notes Sheet I have 3 Columns:
- Column A contains hyperlinks that activate only if the corresponding cell in Column B has a cell address in it. The hyperlink links back to the "Template" Sheet and to the cell address displayed in the corresponding cell in Column B. Column C is simply the text associated with that cell address on the "Template" sheet.
- So, the "Notes" sheet, e.g., has a list of 10 lines of Hyperlinks, cell addresses, and notes in their respective columns.
- The cell address in Column B and the Note in Column C are populated using a Macro ("Submit _Note" attached to a Command button on the "Template" sheet.
- Each time a user submits a note, a new line is created at the bottom of the existing list of hyperlinks and notes.

I would like to add code to the "Submit_Note" Macro that returns the user to the cell that that they just recorded the note for, e.g., user a writes a note in cell C3 of "Template" Sheet and clicks on the command button that runs the "Submit_Note" macro, which copies the note from C3 (and related cell address from C2**) to the "Notes" Tab.
** a separate macro records the cell address which is the subject of the note to cell C2

Thank you in advance.
Incuss

I using the code below to select a starting row and column and an ending row and column. I then copy and paste the data in the next column to the right.

---------------------
ActiveSheet.Range(Cells(SR, SC), Cells(ER, EC)).Select ' Select active range
Selection.Copy
'Set A = ActiveSheet.Range.Select
ActiveCell.Offset(0, 1).Select
'Range.Cells(0,1)
'Range("I15").Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False ' Paste Scores
Application.CutCopyMode = False
---------------
I would next like to perform a Median calculation on the data in the new column using variables to designate the start and end of the median calculation.

Example:

ActiveCell.FormulaR1C1 = "=MEDIAN(Cells(SR, SC):Cells(ER, EC))"

Is it possible to do something like this?

DaveW

I have the below data in a spread sheet, the cell colum and dates change on an hourly/daily basis. I need to search for the date, and select the temp and humidity range for 24 cells across to copy to another sheet.

Doing things the hard way I have managed to find the range I need to copy

=CELL("Address",INDEX($A$15:$BU$15,MATCH($A$5,$A$15:$BU$15,0)))
=CELL("address",OFFSET(INDIRECT($A$6),2,1))
=CELL("address",OFFSET(INDIRECT($A$6),3,24))

Is there a way to use these cell addresses in a Marco to select the range, or a better way to do this?

Thank You in advance
John

Data in red is what I need to copy, the user will input the date to search for on another sheet also.

Date...........15-Feb.....................16-Feb
Hour...........21.......22.......23........0........1.........2
Temp..........76.......76.......75.......72.......68.......66
RH(%).........54.......54.......58.......64.......73.......81

I have two columns of numbers in column A & B. In column A I am trying to locate the maximum value and get the cell address of the number next to it in column B. I am currently using this formula to do this
..... =ADDRESS(MATCH(MAX(A1:A4),A1:A4),2,4)

It works ok, but if the maximum number is the first value in the column it is returning the last cell address in column B. I am not sure what I am doing wrong.

Can anyone show me a better way of doing this?

Thanks for your help.

Is there a formula to use to add a quantity to every cell in a column?

I need to be able to add a quantity of 3 to every row in a column.

Any help would be appreciated.

Thanks!

Hi,

I have a little problem: when excel document is opened I'd like it to memorise last cell address in column A. For instance I use this code in the sheet "ThisWorkbook":
Private Sub Workbook_Open()
    
  If IsEmpty(Range("A3").Next(2, 0)) Then
  RB = Range("A3").AddressLocal
  Else
  RB = Range("A3").End(xlDown).AddressLocal
  End If
  
End Sub
Every time anything is changed (new line is inserted) in "Sheet1" I'd like excel to check if the last cell address in column A changed.
However, the above code does not pass the last cell address for other macroses. After workbook is opened and above macros is run the value of RB becomes "Out of context". Does anyone now how to make excel to memorise last cell address and pass this value to other macroses?

Is there a way to find the first blank cell in a column using a formula?

I am trying to display a selected cells address (Like C1) using the keyboard or the mouse and I would like the address put into a string for later use.

Here is what I have, but it displays the contents of the cell not its address. I am stuck and need some help.

Code:
Sub DisplayCell()
Dim vData
Dim sCell As String
    On Error Resume Next
        Application.DisplayAlerts = False
 
            vData = Application.InputBox _
             (Prompt:="Please click on a single cell, " _
             & "or enter the cell address using the keyboard (ex:C1).", _
             Title:="Input the cell address", Type:=2 + 8)
 
    On Error GoTo 0
        Application.DisplayAlerts = True
 
    If vData  0 Then
        'Convert the cell address into a string
        'using sCell
        'Display the cell address
        MsgBox sCell
    Else
       Exit Sub
    End If
End Sub


I have read about this but i cant seem to figure it out.

I am looking to return the cell address in column A that holds the string "IDK"

I have heard something about a combo of address and match but i am having the worste time figuring it out.

I hope i have explained it correctly.

I have column A, somewhere in column A lies the value "IDK" I would like to have a function that returns that cells address into cell B1. "IDK" will only appear once in column A

In previous versions of Excel, when adding an Auto Filter to a column, a user
was able to select "non-blanks" from the pull down menu which would cause all
cells with no values or text within the same column to be hidden. In Excel
2007, the "non-blanks" option has been removed. How does one collapse all the
non-blank cells within a column using the filter in Excel 2007?

When running a macro to enter date in several different cells, the entries
always are made in the original cell addresses that were used during
recording. I want to record a macro that allows "relative" cell movement
instead of "absolute" (with reference to the starting cell). I seem to
recall there was an option available to do this at one time, but I can't
recall what it was.

I'm using MS Office Excel 2003.

How would I go about using the value in one cell as part of a cell address or range of addresses? For example: cell a1 contains the value 20; and I want to use this value in this manner: sum(b"a1":b30). Or cell a1 contains 20 and cell a2 contains 35; sum("a1":"a2"). Or even a1=C and a2=10; then the cell address to be used in a different formula to use these two values as an address of c10. I've searched for hours and cannot find the answer, surely there must be some way to do this.

a b c
1 20
2 35
3
4 sum(a20:a35)

Hope this makes some kind of sense, I know what I want to do, just can't find the right way to word it. Thanks in advance,

Glen B.
Windchaser@comcast.net.

Hello,

I have a merge area in my excel spreadsheet. I want to find the range of the entire mergearea and then define the same area one row below as a new range or in other words offset the range by 1 row. My code currently is:


	VB:
	
 
    Dim Data As Range 
    Dim Position As Range 
    Set Data = y 
    Set Position = Data.Cells(Data.Rows.Count, Application.Match(x, y, 0)) 
    CellPosition = Position.Address 
End Function 
Function CheckMergeCells(x) As String 
    Dim rng As Range 
    Dim rngStart As Range 
    Dim rngEnd As Range 
    Dim rngMerged As Range 
    Set rng = Range(x) 
    If rng.MergeCells Then 
        Set rng = rng.MergeArea 
        Set rngStart = rng.Cells(1, 1) 
        Set rngEnd = rng.Cells(rng.Rows.Count, rng.Columns.Count) 
        Set rngMerged = Range("A1:D4") 
        Set rngMerged = rng 
        CheckMergeCells = rngMerged.Offset(1, 0).Address 
    Else 
        CheckMergeCells = Range(x) 
    End If 
     
    Sub Check() 
        MsgBox CheckMergeCells(CellPosition("Ground Yarn", Worksheets("Yarn Requirement").Range("A1:XFD1"))) 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem with this code is if I just used rngMerged.Address it returns the entire range, but when I offset it, it only returns the first cell of the range offsetted.

Could somebody point out where I'm doing it wrong and also if there any tutorials out there that explain how to define a range solely using variables, I would appreciate a link.

Thanks in advance

when i am doing the coding

may i know if there is a way to replace the cell address $A9 below:


	VB:
	
Range("A1").Formula = "=IF($A9="""","""",SUMPRODUCT((LEFT(" & myFilePath & "!$C$2:$C$30,8)=$A9)*(" & myFilePath &
"!$D$2:$D$30=B$1)))" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
with a variable for use in VBA?
I have tried using string like below but it doesn't work

	VB:
	
 
CellAddr = ActiveCell.Address 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am struggling to build a simple copy statement that uses multiple variables. I could copy in my current code but it would only highlight the struggle.

A single worksheet holds all the values I need to move to various worksheets in the destination workbook. The destination workbook is MASTER.XLS and is already open. The source workbook has various names.

I have the macro walking through each value in column E of the source worksheet. When a match occurs, the corresponding cell in column F has the destination worksheet name, the corresponding cell in column G has the destination cell address and column H has the destination value (string value).

I have dim statements for SheetName, CellAddr and CellVal ; all set for String. I have been playing with "Offset" as well as "Select"ing through the worksheet hierarchy to drive to the desired destination cell. All seem to be more work than necessary and none work properly.

What I am looking for is a set of macro statements that I can use as a "template" within the balance of the macro I have written. I would also happily accept recommendations about books that provide a step-by-step approach to learning the capabilities of Excel's VBA functions. I know from my limited programming background that there are many ways to do the same thing. I'd rather start with the most efficient rather than burn lots of hours experimenting.

Thanks for any and all contributions.

Hi, is there a way I can use relative cell address in VBA? For example, in the following table:

value item 1 apple item 2 peach item 3 mango
Instead of use

Range("B4").Value = "mango"

to insert mango to cell B4, I can use an address that's one row under peach, or one column to the right of item 3?

Thanks for your help

Good evening everyone! (:

I am new in VBA and since a few days I am stuck while trying to create an hyperlink from the cell of a worksheet to another cell in a different worksheet but within the same workbook.

I am working on two worksheet called COUNTRIES and ERROR CHECK.
"i" and "dc" are two integer variables which scroll all cells in the worksheet COUNTRIES (i all rows and DC all columns) with two DO-LOOP functions, until the first empty cell: which represent the end of my database.
The 2 variables i and DC simply check cell by cell, if there were errors while inputing the data.

So: if the program finds a mistake in the cell H14: the variables will be equal to: i=8 and dc = 14.
If the mistake was in A1, the variables will be equal to: i=1 and dc=1.

Everytime the program finds mistakes in the database (for example a NON Valid Date), then: a WARNING for each specific error is written in the worksheet ERROR CHECK.
The users of the database will read all the warning found: they can click in each WARNINGl, so they will be automatically redirected to the Database (worksheet COUNTRIES), in the exact cell where the error was found... so they can correct the error straight away.

Here my wrong code:

With wbBook 
    Set wsCountries = .Worksheets("Countries") 
    Set wsErrors = .Worksheets("ERRORS CHECK") 
End With 

'.......'

With wbBook 
    Set Selez = wsErrors.Cells(k, j) 
    wsErrors.Hyperlinks.Add Selez, " #' " & wsCountries.Name & " ' ", subaddress= "cell(i,
dc)" 
End With
So:
1)
is the cell with the WARNING in which I want to add the Hyperlink....

2) When people click on the hyperlink: the destination will be the worksheet wsCountries and the cell will be the one in which the input-error was found:
 where i and dc will have the value of the row and the column where the error was found.

The number of hyperlinks created, is proportional to the number of errors found by the program while "checking" the database COUNTRIES.

Concluding: instead of using an explicit address, like...


...I need to replace A2 with the dynamic Variables i and dc.

Does anyone knows how to write the syntax for this?

Thank you very much in advance, I hope it was clear!

Greetings

Emanuele

I thought this would be a relatively common situation but I can't find any examples anywhere.
My data is only 2 columns wide. I have a loop that moves down one column finding the largest value. What I also want to do is store the cell addresses (for the active cell and the other to its right) in a variable before it moves on to the next row.
When the loop is finished I'm looking to use the range variable in the ".SetSourceData Source:=" line when plotting the chart instead of hard coding a range, as the largest value could be anywhere.

This is the start I've made on the code. The two lines I have no idea about are:
"Set rng = Range(ActiveCell.Address & ":" & ActiveCell.Offset(0, 1).Address)"
".SetSourceData Source:=Sheets("Sheet1").Range(rng), PlotBy:=xlRows"

Sub AddNewChart()

Range("I9").Select

Dim chtChart As Chart
Dim bigNum As Integer
Dim rng As Range

Do Until IsEmpty(ActiveCell)

If ActiveCell.Value > bigNum Then

bigNum = ActiveCell.Value

Set rng = Range(ActiveCell.Address & ":" & ActiveCell.Offset(0, 1).Address)

End If

ActiveCell.Offset(1, 0).Select

Loop

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:="Sheet1")

With chtChart
.ChartType = xlColumnClustered
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Sheet1").Range(r1), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Scores"
.HasLegend = False

With .Parent
.Top = Range("F9").Top
.Left = Range("F9").Left
.Name = "MyChart"
End With

End With

End Sub

This must have been done a million times. Can anyone help me?