Visual Basic :: Dynamic Print Range

Copying Dynamic Range Based On Cell Outside Of Range
i am copying a dynamic range of cells from twelve different worksheets. i need to select a range that starts of g10 on every sheet, but ends differently on every sheet. the range ends depending on column A in the last row of each range, the word 'total'. i would like to select the range in column g10 down to the cell corresponding with 'total' in column a in column g. For example, if there is 'total' is in a56, i want to select and copy g10-g56. If on another sheet 'total' is in a68, i want to select and copy g10-g68. there is information below the range. i want to ignore the data below altogther. here is the code that i have now for the first sheet. it grabs G10 through the last cell in column g. any help would be appreciated. thanks

Code:
Sheets("January 2005").Select
Range(Range("G10"), Cells(Rows.Count, Columns("G").Column).End(xlUp)) _
Copy Worksheets("SALES REPORT").Range("a2")

Print Range Of Pages, Find Length Of Document To Print
Hi, I am trying to accomplish two things.
1) I would like to find out how many pages would print out, IF I were to print. ie. Warn the user: "You have asked to print out 500 sheets at $4.50 a sheet."
2) I would like to choose a range of pages to print out, without that range set as the "default" the next time the print function is called. So, my current printing method below is not what I would like. (Unless there is a way to restore defaults?) It displays the normal print dialog, and remembers the last print range entered by the user.

I am displaying web pages on a form within the webbrowser control. Some are very long, so these features are important to me. How it is done is less important. Any advice is appreciated. Thanks!

Here is my very simple example code:

Code:
Option Explicit

'Load a web page
Private Sub Command1_Click()
WebBrowser1.Navigate ("http://www.vbworld.com/")
End Sub

'Brings up the print dialog box on button click.
Private Sub Command3_Click()
Dim eQuery As OLECMDF
eQuery = WebBrowser1.QueryStatusWB(OLECMDID_PRINT)
If Err.Number = 0 Then
If eQuery And OLECMDF_ENABLED Then
WebBrowser1.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_PROMPTUSER, "", ""
Else
End If
End If
End Sub

Private Sub cmdNavigate_Click()
Dim txtNavigate As String
WebBrowser1.Navigate txtNavigate
End Sub

Print Command In Vb6 To Print A Range In An Excel Worksheetsheet
Thank you for you assistance in populating an excel sheet from a vb6 user form textbox.

I am now struggling to print the excel sheet from vb6 command button.

I can print the worksheet but it is blank as I cannot work out how to set the print range.

Assistance would be appreciated as, after hours of trying, I am about ready to scream!!

Dynamic Range Problem
Hi I would like to try something a bit different

I have a form and a named range supplying the sheet names I would like to include in its selection as a drop down combo box.

I have another combo on the same form which I would like to give values conditionally based on the selection in the first combo. It is the source of the second combo I arent sure if it is possible to do.

Here is where the data lies.

On each sheet listed in the first combo. There is a variable number of columns in row 2 starting at column B so I need to use a dynamic range.

I know how to do this using a named dynamic range but not a generic? dynamic range.

For example

the first combo selection is "sheet1"

the second combo contains options from B2, B3 & B4 on the corresponding sheet (so 3 selections here)

if the user had chosen in the first combo "sheet2"

there are 5 options in B2, B3, B4, B5, & B6

is this possible?

Thanks for your help,

Rob

Excel Dynamic Range ?
I have seen this done somewhere..

Can i have a a range object that will grow with my expanding
list..

like myrange = range c3 to range EOF

??

That is all
Seahag

Applying Formatting To A Dynamic Range In VB
Hi all,

I'm using VB to populate a spreadsheet in excel, and everything has been pretty straightforward until now. I've got my data all nicely entered, but one column needs to have a background color applied to it. I don't know how many object are being entered, because it varies depending on the data set the report is run off of. Ideally, I just wanted to apply my formatting to one row, then insert new rows based off that row as needed, but I couldn't get the row insertion code to work for the life of me.

I need to find a way to select the range (always starts at cell C8) up to its ending point, and apply my formatting rules. Does anyone know of a way to do this?

Also, while on the subject, if I wanted to insert a row with a certain height, how would I do that?

Any help is appriciated.

Sum A Dynamic Range In Excel From Access VBA
I have a series of spreadsheets generated from a report in MSAccess. Each spreadsheet contains a different number of transactions.

I need to sum a column of numbers in this dynamic range in one specific column and put the total in the cell below the last number. Would someone have the VB code to perform this task?

Dynamic Array's 'subscript Out Of Range'
hi there,
i working with a dynamic array and 'ReDim'ing it in section where there is possibility of exceeding limits tab is generate , but its giving the error no. 9 ,'subscript out of range',th moment it comes across another redim statement in the code..eve if the subscript is not exceeded...
please help as i haven't worked with dynamic arrays before...

thanx in anticipation.

regards,
Anu

Dynamic Named Range To Fill Combo Box
i created a dynamic named range called MyRange on one sheet (Sheet1) and it selects from the top of column A to the last entry.

How do i use this named range to fill the combobox list on a different sheet (Sheet2)?

Is there a property i can set to it?

Dynamic Range In Charts Automaticly Created
hi every1,
this is my first post. i'm asking for your help because i have a little problem.
i'm new to vba in excel. i have reading some post about this matter but i don't find a way to have it work,

i have a excel file that load a txt file . with some code i format a worksheet with the data., in the first sheet i have 2 fixed chart with fixed range, then i create an another chart with other data. in the same sheet a have a combo box that i use to change the values, for the automatic chart i need to change it data range.

how do i change it

i attacht the files for anyone who can help me.

thanx in advance, and sorry for my poor english.

Creating A Pivot Table From A Dynamic Range
How can I get this:

Code:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="tempRapport!R1C1:R6549C4").CreatePivotTable

to be something like this:

Code:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="tempRapport!R1C1:Selection.End").CreatePivotTable

What I want is to dynamically select the data area for my pivot table because the range will vary from time to time.

Can you help?

Data Validation With A Dynamic Named Range
I am using a Data Validation list. For source I have '=RepList'. I have defined the range RepList using the following: =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

Everything seems to work perfectly at first glance. The drop down list dynamically adjusts itself to match the data I have in the A column of Sheet2.

The problem is this: even though I have I have checked the box that says "show error alert after invalid data is entered" the cell accepts invalid data and no error message is displayed.

Any ideas?

Excel - Setting A Dynamic Range Of Cells
What I'm trying to do:
My application exports a set of search results to Excel and leaves me in cell A1. I want to select the search results and name that range 'DataSet'
Obviously, the search result size will vary, so I need the last line to refer to some variable set or offset instead of R8C6.
I have the variables myRowCount and myColcount. Are they useful?

THANKS
Code Below:

Selection.CurrentRegion.Select
myRowCount = Selection.Rows.Count
myColCount = Selection.Columns.Count
ActiveWorkbook.Names.Add Name:="DataSet", RefersToR1C1:= "='Case List'!R1C1:R8C6"

Create Chart Using Dynamic Range (Q146055)
I am attempting to use Q146055 to build chart using a macro.  I have included sample data and code below.  I added Dim statement for myrange and mysheetname. I am getting the following error

Runtime error '1004'
Method 'Range of object' _Global failed

Debug jumps to Range ("c4").Select

I am using Microsoft Excel 2002 (10.4302.4219) SP-2 and
Microsoft VB 6.3

I have tried to generate code for building chart by recording macro, but have been unable to incorporate into subroutine below to get it to work.

,Region 1,Region 2,Region 3
Jan,10,80,15
Feb,20,70,25
Mar,30,60,35
Apr,40,50,45

Option Explicit

Sub Createchart()
Dim myrange, mysheetname As Variant
' Select the cell in the upper-left corner of the chart.
Range("c4").Select
' Select the current range of data.  This line of code assumes that
' the current region of cells is contiguous - without empty rows
' or columns
Selection.CurrentRegion.Select

' Assign the address of the selected range of cells to a variable
myrange = Selection.Address

' Assign the name of the active sheet to a variable is
' used in order to allow a chart to be created on a separate chart
' sheet.
mysheetname = ActiveSheet.Name

' Add a chart to the active sheet.
' ActiveSheet.chartObjects.Add(125.25, 60, 301.5, 155.25).Select

'To create a chart on a separate chart sheet, remark out the
'previous line, and substitute the next line for te one above

'Charts.Add

Application.CutCopyMode = False

'This line can best be written by  recording a macro, and
'modifying the code generated by the Microsoft Excel Macro
'recorder.

ActiveChart.ChartWizard _
    Source:=Sheets(mysheetname).Range(myrange), _
    Gallery:=xlLine, Format:=4, PlotBy:xlRows, _
    Categorylabels:=1, SeriesLabels:=1, HasLegend:=1, _
    Title:="", CategoryTitle:="", _
    Valuetitle:="",, ExtraTitle:=""
End Sub

**RESOLVED** Dynamic Named Range Problem
All,
I have a problem using Dynamic Named Ranges to feed some VLookups.

I have attached an image of my workbook to show the problem (would take to long to replicate verbally!). The idea is the row range in which the argument from cell A15 exists within the table, then examine this row range for column B, searching for the argument from cell A20.

The problem is that the dynamic named range fails to calculate properly.

Can anyone help out on this?

Thanks,

Matt

Edited by - M_B on 12/22/2005 7:32:27 AM

How To Set Print Range
Hello all

I have made print preview. But it was large that A4 size. I am notgetting that how to set print range and if it overflow then it goes tonext page.

And how to give next page for print preview and printer

Thanks

Amrit

Print A Range Of Dates
I'm trying to pull appointment dates from a database and just print them into a picture box. How do I search for the dates? I have two input boxes: one with the start date and one with the end date. Those dates, and the dates between them need to print into the picture box. I have no clue how to go about this. Here's what I have so far:

Private Sub mnuAppointments_Click() 'on a drop down menu

Dim strReportDate1 As String, strReportDate2 As String, foundFlag As Boolean

strReportDate1 = UCase(InputBox("Enter the Start Date (MM/DD/YY):"))
strReportDate2 = UCase(InputBox("Enter the End Date (MM/DD/YY:"))

If Len(strReportDate1) > 0 Then
datContacts.Recordset.MoveFirst
foundFlag = False

Do While (Not foundFlag) And (Not datContacts.Recordset.EOF)
If Trim(UCase(datContacts.Recordset.Fields("Next Appointment").Value)) = strReportDate1 Then
foundFlag = True
Else
datContacts.Recordset.MoveNext
End If
Loop

If Not foundFlag Then
MsgBox "Please try again"
datContacts.Recordset.MoveLast
End If
Else
MsgBox "Please enter a start date"
End If

Print A Worksheet Range From Vb 6.0
G'day All,

I have an application in vb 6.0 that collects data, saves it to a database then collects the data from the database and sends it to an excel worksheet from where it is printed.

My problem is that I need to print a specified range of cells (not the whole worksheet) for one of the reports.

Can anyone help me with the code for a cell range?

Code:

Dim comRpt As Command
Dim strErrorMsg As String
Set comRpt = Nothing
Set comRpt = New Command
Set rstReportDetails = New ADODB.Recordset

Set AppExcel = CreateObject("Excel.Application")
Set wBook = AppExcel.Workbooks.Open("S:Application-Res aApplication-all.xls")
Set wSheet = AppExcel.ActiveWorkbook.Worksheets("Credit")

wSheet.Cells(5, 3).Value = rstReportDetails("ApplicationNumber")
wSheet.Cells(5, 5).Value = rstReportDetails("LoanAmount")
wSheet.Cells(26, 2).Value = rstReportDetails("Description")
wSheet.Cells(27, 2).Value = rstReportDetails("MainPurpose")
wSheet.Cells(29, 1).Value = rstReportDetails("Conditions")
wSheet.Cells(33, 2).Value = rstReportDetails("CurrentlyWith")
wSheet.Cells(11, 4) = frmCreditCheck.txtGeneral.Text

Call wSheet.PrintOut
Any help would be appreciated. Thanks.

Print Range In Excel
How can i set the print range in excel so that it will always print 1 page, if i use printArea it prints 2 pages instead of 1. Is it possible to always print to 1 page?

VB Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$R$55"

Clear Everything Outside Of Print Range
I am using the following sub routine in VB to clear all data in cells and columns outside a designated print range.
The problem is it only clears out up to Z. There are a few columns AA - AF that gets left. Why is this?

Cna you help?

-------------
Next shp

        If sht.Name <> "Wellhead Data Sheet" Then
        If sht.PageSetup.PrintArea <> "" Then
            lRight = Right(sht.PageSetup.PrintArea, _
              InStr(sht.PageSetup.PrintArea, ":"))
            uLeft = Left(sht.PageSetup.PrintArea, _
              InStr(sht.PageSetup.PrintArea, ":") - 1)
            Set rngClear = sht.Range(lRight).Offset(0, 1)
            rngClear.Select
            Range(Selection, Selection.End(xlToRight)).EntireColumn.Delete
        End If
        End If
        Range(uLeft).Select
        sht.Protect ("ciw")
---------------

Print Block Of Range
I have this sheet with 5 block of range.
How to make a macro and assing to the button in this mode:

Set range from A to P (last cell in this case cont last cell from A) and print a copy
Set range from R to AG (last cell in this case cont last cell from R) and print a copy
ecc...

Peraphs is a init:

Option Explicit
Sub ShowUsedRange()

Dim ELENCO As Worksheet
Dim RIGA_MAX As Integer
Dim COLONNA_MAX As Integer
Dim I As Integer
Set ELENCO = Sheets("STAT_NEW")
RIGA_MAX = ELENCO.Cells(65536, 1).End(xlUp).Row
COLONNA_MAX = 16
With ELENCO
For I = 1 To 5
ActiveSheet.PageSetup.PrintArea = "$A$1:" & Cells(RIGA_MAX, COLONNA_MAX).Address
'HERE THE COMMAND TO PRINT OUT AREA
COLONNA_MAX = COLONNA_MAX + 16
Next I
End With
End Sub

Select Print Range
I have multiple charts on one sheet. When printing i want to select both the charts to be selected..either print on one page or more than one page
Can anyone tell me how to do it through VB ?

-ash

Print Data Between A Date Range
I have a data base with a long list of enteries which I would like to beable to use a macro to print enteries between to dates. If having it print is to hard how do I get to copy the enteries between the dates to a new worksheet

Appending A Range To A Print File
Yikes.

Haven't been here for a while...

Seem to be going down a lot of dead end threads with this one.

Need to print various ranges to either the active printer &/OR append to a file.
To the active printer,
myrange.printout is working great
to append to a file i just seem to get more lost.
- does not seem to be a vba utility?
- shell(whatever) is getting me lost... no file
concatenate function in DOS?

help greatly appreciated.

* * * *.. i keep hitting tab here and, duh, of course going to the "Submit NEw Thread" button. Hope i haven't inadvertently submitted any incompletes or extras

thanks again..
kak5157

Use A Macro To Print An Area, But Range Is Always Different
Hi everyone,

I have a macro that formats some data for me. I use this macro very often and I would like it each time that it is run to create the right print range, so that I can immediately print of my sheet if I need to. The problem I have is that although I always have the same number of columns at the same width but there are always more or less rows than the last time I printed. How do I get VBA to recognise this and select the print area that has data only. Also, there are some rows in my sheet that have no data...is this a problem (they are in between data though).

Cheers

Max

Print Range In Excel From Vb Application
I have several vb user forms which populate an excel spreadsheet. I need to put a command button on a user form which will print out the excel sheet that has been populated. At no time will the user see the excel workbook.

I have managed to get as for as printing the sheet but the print-out is blank as I have been unable to work out how to tell the printer what the print range is.

I realise that this must be a very basic question but I would appreciate some help.

Print Report With Dynamic Selected Columns
i have very serious problem...
i create a project and one of my sub program required to print a report function.
so i design a program for this purpose..this sub program contain a DataGrid(DataGrid1), Text (Text1), and several buttons (cmdPrint, cmdExecute, cmdClear, cmdClose)..
the program is work like that..
user have to key-in the SQL command in the Text1 then click on the the execute button then the datagrid will display all the data from the SQL command....is working at here...
then i have to make the report rigth ??
here is the problem....(i used datareport as my report generater..)
when user key-in "select * from Table1" ....it work fine(select all columns)...
but if user key - in something like....."select Name, age from Table1"
then a problem is come up....an error message prompt up...
"datafield "address" not found...
(the address is one of the coulmns but this case i did't to select it...bcoz i don't want to print....)
so do u guys understand what is my purpose ?? my program want to allow users to select the column they need to print only....and not to print all.....
is there any solution ?? can give me any source code relevent to this program??
urgent....please.......................

Print Dynamic Values Of A FORM Using PrintDialog
Hi there,

I have this bug in the program that I am developing. The problem is I am trying to print a form using the printdialog control. Everything works fine if I just print the form with default values set in the .text property of the labels in the form..... which is not what I want. I am updating the label controls (text property) using variables. I can see the form with the updated text on the labels... but when it gets printed it's printing the default values. Am I doing something wrong here... please suggest.

Here goes my code:
Code:Private Sub printDocument1_PrintPage(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
DrawAll(e.Graphics)
    End Sub

and my DrawAll routine:

Code:Private Sub DrawAll(ByVal g As Graphics)
Dim srcRect = New Rectangle(0, 0, Me.Width, Me.Height)

        Dim nWidth As Integer = PrintDocument1.PrinterSettings.DefaultPageSettings.PaperSize.Width
        Dim nHeight As Integer = PrintDocument1.PrinterSettings.DefaultPageSettings.PaperSize.Height
        Dim destRect = New Rectangle(0, 0, nWidth, nHeight / 2)
        Dim scalex As Single = destRect.Width / srcRect.Width
        Dim scaley As Single = destRect.Height / srcRect.Height
        Dim aPen As New Pen(Brushes.Black, 1)
        Dim i As Integer
        'MessageBox.Show(Me.Controls.Count)
        For i = 0 To (Me.Controls.Count) - 1
            If Controls(i).GetType() Is Me.lblAdditionalValue.GetType() Then
                Dim theText As Label = CType(Controls(i), Label)
                g.DrawString(theText.Text, theText.Font, Brushes.Black, theText.Left * scalex, theText.Top * scaley, New StringFormat)
            End If
        Next i

    End Sub

I am held up here.. please suggest if I can do it any other way!!! Thanks

Mally

How To Create Dynamic Printers(Print To File)?
Hi,
I am trying to create dynamic Printers(Print to File) based on User Login. I am trying to use PrintUI.dll. But it would just let me create a regular printer on a standard port. It wouldn't let me create a "Print-to-File" Printer. Any thoughts?

I am able to create printer by using this command
rundll32 printui.dll,PrintUIEntry /if /b "Test Printer" /f %windir%inf
tprint.inf /r "lpt2:" /m "HP Laserjet 4000 Series PCL"

but when I am trying to create "print to file" printer using a print file name("c: est.prn:") using the following code
rundll32 printui.dll,PrintUIEntry /if /b "Test Printer1" /f %windir%inf
tprint.inf /r "c: est.prn:" /m "HP Laserjet 4000 Series PCL"
I am getting error: arguments are invalid.

Can anyone tell me why my code is not working or whats wrong with this code.
Please help me.

Thanks

Hussain

Setting A Range Equal To That Of The Print Area
I have found code that will allow me to attach a certain range, on an excel sheet, into an outlook e-mail and then send it.

However, it requires you to select the range.

The range I want sent, happens to be the same range as the printable area.

Is there a way to just have the rangeToSend = printArea?

VBA Fill Formulas In Worksheets And Print Range
:huh: Hiya

Maybe you can help me with this one

Do you know what code I would need to fill down formulaes in subsequent worksheets depending on figures in a master sheet. Say Range("a3").Value

For example if the master sheet had 1 apple, 2 oranges and 3 tomatoes, how could it recognise the number of each and copy the last row of formulas according to the number?

I would then require the code to copy down the last row of formulas twice in the oranges worksheet and three times in the tomatoes. Basically it needs to be smart enough to formulas according to the number inserted in the first sheet.

Finally I need the macro to print say the last twenty rows of formulas in each worksheet ( a set print area which will encompass the new formulas that have been copied down).

It sounds easy but I think it is much more difficult

Here is the code I am using for the copy fill
Code:
Range("F24:G24").Select
Selection.Copy
Sheets("Macq True Index FI- FIFO").Select
Range("A130").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Data Sheet 106").Select
Range("C24:D24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Macq True Index FI- FIFO").Select
Range("A131").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C129:H129").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C129:H131")
Range("C129:H131").Select
Range("H130").Select
Sheets("Macq True Index FI- FIFO").Select
Range("A130:A131").Select
Selection.Copy
Sheets("Macq True Index FI (Gains)").Select
Rows("135:135").Select
Application.CutCopyMode = False
Selection.Copy
Rows("136:137").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B137").Select
Selection.ClearContents
Range("N136").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-12]=0,0,IF(RC[-7]-RC[-6]-RC[-5]=0,((RC[-12]-R[-1]C[-12])*RC[-4])," & _
"((RC[-7]*(RC[-12]-RC[-11]))+(RC[-6]*(RC[-11]-RC[-12]))+(RC[-5]*(RC[-10]-R[-1]C[-12])))))"
Range("N137").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("B137").Select
ActiveCell.FormulaR1C1 = "1.001"
Range("B138").Select
End Sub

Common Dialog/PrintDialog API Print Range
All,
I have exhaustively searched this website as well as other to find how to activate the Current Page or the Pages radio options on either the commondialog control or the PrintDialog API. If anyone could point me in the right direction that would be helpful.

I have been setting the .Flags to cdlPDPageNums, in fact, I have tried all available flags in some combination. Maybe there is a trick to an And/or clause in the setting of the flag that I am just not getting correct.

Thanks in advance

VBA Excel: Combine Print Range On Same Page
Assign multiple print ranges within a single Excel Worksheet

Is this performed through a pagesetup function? Please get me started...thanks

Private Sub CommandButton1_Click()

Dim rCol As Range
Dim rCell1 As Range, rCell2 As Range
Dim Sht As Worksheet

'Set range variable to Selection
Set Sht = ActiveSheet
Set rCol = Sht.UsedRange.Columns(1)
'Insert page breaks
Sht.PageSetup.PrintArea =

How To Create Dynamic Form By Clicking A Dynamic Control Placed In Dynamic Form?
Hi all,

I have generated a dynamic form at run time inside which i have placed a dynamic command button. I need to open another dynamic form and place controls in the second form on clicking that command button. Can anyone please help me to come out with the solution?

Thanks in advance,
Devi

Loop Through Named Range And Lock Range Name Based On Values
ok..basicallly I created a worksheet called 'calendar'
this calendar worksheet has 12 named ranges

rngJanuary,rngFebrary....to rngDecember(yes its a calendar)

all of these named ranges are also inside one big range called rngcalendar.

Basically I want to accomplish the following.

when the workbook get opened get the last day of the last month the user entered a value and lock every other named range after the month of the named range the last value was stored in.

eg..if on march 20th the user entered a "w" as a value I want to lock
RngApril through rngDec.

in other words force them to finish entering all values for a month before continuing to the next.

if IT helps all my days of the month are in ltgrey coloring
and the vales the enter go below the day value
in normal white cells(see attachment)

I already validate the entried in the
Private Sub Worksheet_Change(ByVal Target As Range) event but dont know how to accomplish the above step.

Excel Range In Variant Array, 'Subscript Out Of Range' Error
Morning Guru's

I have been having problems with this function for a few days now. I'd be most grateful if one of you could set me straight with this. Here is my code:

Code:

Private Function RemoveNulls(ByVal srcRange As Excel.Range, ByVal lastRow As Long)
MsgBox srcRange.Address

Dim i As Long
Dim xlVals() As Variant
xlVals = srcRange.Value
For i = LBound(xlVals) To UBound(xlVals)
If xlVals(i) = -9999 Then xlVals(i) = 0
Next i
srcRange.Value = xlVals
Set srcRange = Nothing
End Function

The compiller gives me the 'Subscript out of Range' error on the line:
If xlVals(i)=-9999 Then xlVals(i)=0

In-fact if I try to get a msgbox or something to display any value in the array, I get the same error. The Lbound and UBound functions are returning the expected values, therefore I believe that the array definately contains data!?

Any Ideas Amigo's?

Thanks

Determining Wether Current Cell/range Within A Specified Range
Hi,
I am trying to write a macro for excel spreadsheet.
I am trying to find some function or method to determine wether a particular cell is currently within the specified range or not.

A simple example to illustrate question:

Dim Action_Range as Range
Set Action_Range = Range("A1:H:10")

Dim Current_Range as Range
Set Current_Range = ActiveCell

If Current_Range is part of the Action Range then
'do something
Else
'ignore
End if

Can somebody give some suggestions?
Thanks

Excel Macro, Subscript Out Of Range, But Its In Range?
i have a 2d array:
wds(1 to 24, 0 to 1)
the values in the array are set up correctly, but when i try to
output the values of the array to a cell i get the subscript out of
range error.

Below is the code that ive used to write to the cells, to test
that the array has the correct values the commented out
msgbox was used. (it returned the correct values)
except when i use the same code to assign the value to a cell's value i getthe error.

Code:

dim count1 as integer
count1 = 4
For counter2 = 1 To 24
'MsgBox (wds(counter2, 0))
Sheets("Doubles Results").Range("C" & count1) = wds(counter2, 0)
Sheets("Doubles Results").Range("E" & count1) = wds(counter2, 1)
count1 = count1 + 1
Next counter2

thanks for any replies

ps.
with other languages ive used to do simple calculations like
variable += 3 to add 3 to the variable
variable ++ to increment the variable
is there something similar to this in VB?

Excel: Named Range Will Not Accept New Range Value
I have 2 worksheets in the same workbook
On worksheet "PIR-DT MTH" there is a cell (i.e. "E3") which contains a string value representing a range of cells, eg. "C4:L32"
This range value changes due to specific triggers which aren't important
In "PIR-DT MTH", there is a named range called "PIR1DB" with the same cell range that is stored in the cell "E3"
On the other worksheet "PIR-DT CAT", there is a pivot table "PIR1DTCAT" which has it's data range defined as the named range "PIR1DB" on the previous worksheet
So, the jist of the idea is that when the content in cell "E3" on "PIR-DT MTH" changes, I update the range that the workbook Name "PIR1DB" refers to
When the named range is updated, the pivot table "PIR1DTCAT" will be refreshed to show data for the changed range
I've toiled over this code over and over and over and all the variables show the correct values (using the VB Debug window)
But for some strange reason, the line which is supposed to set a new range to the Name doesn't work at all

The call to the function looks like this:
Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT", "DTCAT", "1", "E3")

The actual function looks like this (I have placed the actual values in comments at the end of the line so you can follow what is happening):

Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName As String, _
                                ObjWkSheetName As String, InfoType As String, _
                                IRNumber As String, RangeInfoCell As String)

Dim myexcel As Object
Dim myworkbook As Object
Dim sourceworksheet As Object
Dim objworksheet As Object
Dim PivotTableName As String
Dim PivotSourceData As String
Dim NameRef As String

    Set myexcel = GetObject(, "Excel.Application") 'Point to active excel application
    Set myworkbook = Excel.Application.Workbooks("IRReports.xls") 'Point to the relevant workbook
    Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName) 'Point to the relevant worksheet
    Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point to the relevant worksheet

    PivotTableName = "PIR" & IRNumber & InfoType    ' PIR1DTCAT

    If sourceworksheet.Range(RangeInfoCell).Value = "None" Then    ' C4:L33
        'There is no downtime data, hence the pivot table will not be updated
    Else
        PivotSourceData = "='" & sourceworksheet.Name & "'!" & sourceworksheet.Range(RangeInfoCell).Value    ' 'PIR-DT MTH'!C4:L33
        NameRef = "PIR" & IRNumber & "DB"    ' PIR1DB
        myworkbook.Names.Add NameRef, PivotSourceData    'This is the line which doesn't work
        objworksheet.PivotTables(PivotTableName).PivotCache.Refresh

        myworkbook.ShowPivotTableFieldList = False
        Application.CommandBars("PivotTable").Visible = False
    End If

End Sub

Changing the Named range doesn't work
The source variable (i.e PivotSourceData) would have the correct information in but and after the line is executed and you check the source range for PIR1DB is says something completely different like "='PIR-DT MTH'!BJ9:BS38"
I have no clue where it gets this from
And everytime you run the code, the strange range changes
Even when I hardcode the value (see below) , it still doesn't work
myworkbook.Names.Add "PIR1DB", "='PIR-DT MTH'!C4:L33"

This is just weird, the fact that hardcoding the values doesn't work means something is drastically wrong somwhere
Am I using any predefined VB words?
Is the sky falling?

Searching A Range And Copying Values Into Another Range
Hi all.

Can someone help me with a problem I have. Working in Excel, I have a range (B11:E15) that looks like:

Date    Consumption    Limit    Excess
01/01/04    1100        1000    100
02/01/04    980        1000    0
03/01/04    760        1000    0
04/01/04    1200        1000    200

What I need is code that will search through the column called 'Excess', and for any values that are greater than 0, copy the entire row (values only, not formatting, or equations that may be behind the scenes) into a new range starting at G12. The code will search each row of the data and for any other rows that have an 'excess', VB will paste it immediately beneath any existing entires in the new range. What I want to be left with is:

Date    Consumption    Limit    Excess
01/01/04    1100        1000    100
04/01/04    1200        1000    200

I hope that anyone can help with this as im brand new to VB. I've been searching many forums for people asking similar questions, but i'm having a hard time of it.

Many thanks to anyone that can help.

Looping A Range Format Identical Range.
Hi Guys and Gals,

Ive been working on this project and needed a simlpe loop though a set range and check for 5 different values, if the values are present format a cell with the identical value on another sheet.

Code:
Sub LeaveTravelOfficeSick()
    Sheets("Leave-Travel-Office-Sick").Select
    Dim i As Integer
    Dim rng As Range, rng2 As Range, rng3 As Range
    Dim rngAll As Range, rngall2 As Range, rngall3 As Range
    'lngNumRows = Columns("Y:BA").CurrentRegion.Rows.Count - 1
    'Set rngAll = Range("N3:R" & lngNumRows)
    Set rngAll = Range("E6:E10000")
    'Set rngall2 = Range("F6:F10000")
    'Set rngall3 = Range("G6:G10000")
    j = 6

    For Each rng In rngAll
        With rng
            'If Not IsError(.Value) Then
            Select Case .Value
                    Case "1/2 Leave"
                       With Worksheets("crew").Cells(j, 17)
                        .Value = "1/2 LEAVE" ' displays name OFFICE as cell value
                        .Interior.ColorIndex = 1 ' colours cell black
                        .Font.ColorIndex = 27 'colours text yellow
                       End With
                    Case "Leave"
                       With Worksheets("crew").Cells(j, 17)
                        .Value = "LEAVE" ' displays name LEAVE as cell value
                        .Interior.ColorIndex = 1 ' colours cell black
                        .Font.ColorIndex = 27 'colours text yellow
                       End With
                    Case "Sick"
                       With Worksheets("crew").Cells(j, 17)
                        .Value = "SICK" ' displays name SICK as cell value
                        .Interior.ColorIndex = 1 ' colours cell black
                        .Font.ColorIndex = 27 'colours text yellow
                       End With
                    Case "Office"
                       With Worksheets("crew").Cells(j, 17)
                        .Value = "OFFICE" ' displays name OFFICE as cell value
                        .Interior.ColorIndex = 1 ' colours cell black
                        .Font.ColorIndex = 27 'colours text yellow
                       End With
                    Case "Travel"
                       With Worksheets("crew").Cells(j, 17)
                        .Value = "TRAVEL" ' displays name TRAVEL as cell value
                        .Interior.ColorIndex = 1 ' colours cell black
                        .Font.ColorIndex = 27 'colours text yellow
                       End With
            End Select
        End With
    j = j + 1
    Next rng
End Sub

I built this sub above, it loops through every cell on sheet 1 column E starting at cell E6 and stopping E10000.

It then formats the corresponding cell on sheet 2.

Sheet 1

date jim andy simon
01/01/2007 sick travel sick
02/01/2007 sick sick sick

Sheet 2

date jim andy simon
01/01/2007
02/01/2007

So when the loop finds sick in 01/01/2007 for jim, it then formats the same cell on sheet 2 with sick.

The above sub only does 1 name, to do muliple names i do the same For Each rng In rngAll but just increase the number by 1 and the range value to the next column.

Is there a way to do it with code to save me recreating the loop for 40 more employees?

Any help would be great, will save alot of copy/paste and find/repace values

Al

Edited by - Big Al Inc on 6/19/2007 7:11:07 AM

RESOLVED: Finding A 'range' From Within A 'range'? - Excel VBA
Relative newbie...

Wanting to get coding that will perform a search through a spreasheet range to find a unique row of data (i.e. a match to the contents of first 3 columns), returning the row No. where the match is found. I had thought that a 'range.find' would accomplish this, but have since been advised otherwise.

Anyone any ideas????

Adjacent columns in the row are for additional pertinent data. The search will be used either to retreive the additional data, or, to add to or change it.

thnx in advance,
remalay

Edited by - Remalay on 8/22/2006 6:50:32 AM

Dynamic Forms With Dynamic Controls
I have an mdi form with a couple of child forms on it. these children forms are pre loaded with controls with an index of 0. i create an instance of these children forms and load some controls at run time. in some cases (like a label control) the controls are not visible. and in another it gives run time error 340 (control array element 1 does not exist).

Hope someone can tell me what gives this kind of error?

TIA

ferdz

Excel Named Range --> Vb Range
Hello,

I have an excel spreadsheet which has a named ranged which defines a table.

in vb.net i want to open the workbook goto the sheet (this i can do) then copy the table using the named excel range stored in the worksheet and then paste this table into powerpoint.

I do not know how to refrence the named range in excel. is it part of the worksheet property? i have not been able to figure that out.

i also might have problem with the pasting into powerpoint. when i paste the data will the grid from excel show up or can i paste it in as a picture?

Thanks

Copy A Range Beneath Another Range
I have two columns with values. I want to copy a range beneath another range how can i do that. I shall explain it with an example. It must be in a macro...

Column A-----Column B-----
10------------- 50
20------------- 60
30------------- 70
40------------- 80

what i want is :

Column A---
10
20
30
40
50
60
70
80

The problem is when i use

....
Selection.End(xlDown).Select
.paste
....

then i past the range in value 40 in column A. I must copy BENEATH column A, how can i realise this

Something like
Selection.End(xlDown+1).Select ??

Thanks in advance

Range Selection: Unknown Range
I'm trying to find a way to select a range based on other values. Range() uses exact cell names (A1 etc). But since the # of rows can vary, exact cell names won't always be valid for any amount of data.

for example,
A7 will always be used, but the last part of the range can vary depending on the # of rows present.

i'm using a user defined function LastRowInColumn() to return the # of the last row present, and i'm using another function to return the correct column #
example
x=LastRowInColumn(2)
y=column#

The problem i'm having is using those 'x' and 'y' variables

What i would need it do to is something like this:

Range("A7:yx").select <--- where y and x are the variables above. Of course this doesnt work because Range() expects exact cell names. I've tried messing with the Intersect function but still the same problems. Any ideas?? Thanks

Dynamic Print Range

Hello to youI need a subroutine that will look at a worksheet and identify the last cell that contains data and set the print area up to and including that last cell. It must be renewable - i.e. it works every time new data is inputted onto the sheet.Can anyone help me?????

Hello to you

I need a subroutine that will look at a worksheet and identify the last cell that contains data and set the print area up to and including that last cell. It must be renewable - i.e. it works every time new data is inputted onto the sheet.

Can anyone help me?????