Free Microsoft Excel 2013 Quick Reference

Range query Results

Hi All,

I have a 'simple question' for you Excel buffs out there ...

I have a dynamic range - using Offset & Count - that is used to keep track of imported numeric data - one decimal place.

After the import and divers manipulation of the data, I then proceed to statistical analysis of the Rows/Cols of the range, the results of which are written into the sheet, 2 rows below the last data row and two Cols to the right of the last data column.

What is irritating - but not yet 'serious' - is the fact that the dynamic range extends itself to the right, leapfrogging the blank columns to take in 6 of the 8 columns of my statistics - there seems to be a 'pattern' here ... miss two blanks and then ignore two numerical columns ???, but it still escapes me.
This behaviour is not duplicated vertically, the range stops at the last row of data.

I have dozens of workarounds - all untidy / clumsy - and I would really like to understand what is going on, a.) to perhaps avoid this, b.) learn a more elegant programming technique.

Best regards

David D.

I work in the import administration of a large importer. I have an excel spreadsheet with 3 columns:

for example:

Early ship date Late Ship Date Actual shipdate
2005-07-01 2005-07-05 2005-09-30

I would like to query using a formula whether the Actual shipdate is earlier than early ship date, OR later than late ship date OR is in between the Early and Late ship window.

Any ideas?

Does any body know how to query whether two ranges overlap in VBA?

I have a range containing the week no, date, person and have named the range PersonSearch.
I want to search for this information in another worksheet named Notify.
I'm having trouble with my code, is it because the data is mixed ?
Any help would be appreciated.
My code is
,Dim FindP As Range,
,Set FindP = Worksheets("Data").Range("PersonSearch").Value,

,If FindP = "" Then End,

' Goes to the start of the information
, Sheets("Notify").Select,
,Range("A2").Select,

' Tests current cell against FindP information
,Do
, If ActiveCell.Value = FindP Then,
,Call GettingData,
,Exit Sub,
,Else: ActiveCell.Offset(1, 0).Select,
,End If,
,Loop Until ActiveCell.Value = "",

I'm trying to write a couple of lines that will check whether a cell (cel1) is contained within a specified range, but i can't get the right commands right. Any ideas.
thanks

If cel1 Range("B3:AF4,B7:AF8,B11:AF12,B15:AF16,B19:AF20,B23:AF24,B27:AF28,B31:AF32,B35:AF36,B39:AF40,B43:AF44,B47:AF48,B51:AF52") Then
MsgBox "oops", vbOKOnly, "Error"
Else

Hi There,

I'm trying to identify if someone was active during a specified month using their start date and end date. I have tried an IF statement but can't seem to include dates between a range ....Ideally I would like a yes or a no answer.

EmployeeStart DateEnd DateDec-06Jo Blog11/01/200601/12/2006Yes/NoJoan Blog11/01/200611/11/2006Yes/No

Many Thanks

I have a few graphs which need updating on a daily basis
I have set up a macros to be run each day so that when its run it automatically changes the range in the source data by extending the column range by 1.

eg Day one it picks up row50, col a:a
Day two it picks up row50, col a:b
Day two it picks up row50, col a:c...........and so on

However, the vba in my macro refers to row numbers, which will not automatically change if rows are inserted into the sheet from which the graphs are picking up the data.

is their any way around this?

I'd like to use the following code

but I want the "E"s to be the value of "counter". How do I code this?

Thanks.

Hi all,

Okay, may be a easy solution but this has been driving me nuts as a novice.

I have a three column bank statement which follows:

Column a = date of transaction
column b = description of transaction (normally a line of text)
column c = amount

Now on a separate worksheet in the same document i want to be able to run a formula that returns a value for the amount spent in a certain date range on particular types of transaction.

For example:

- Value returned for wages spent in march
- Value returned for atm withdrawls in march.

For example I may have transactions described as "BILL PAYMENT TO J DENNEY WAGE REFERENCE J Denney MANDATE NO 0013"on the statement. So as well as the date range query I would want to run the query with 'contains' "denney wage". Now this is where i get stuck, as i have no idea how to run a contains formula, rather than equals. Especially one that allows me to search for a part string of text rather than exact.

Any help appreciated.

Ta

James

I know that if you allocate a pointer ( handle ) to a worksheet, range, query
table, etc. and don't release it then when you call quit from the application
the Excel process isn't released. I have no problem until I call Refresh on
a Query Table. I programmatically using C++ create/start the application,
workbooks, workbook, worksheets, worksheet, range, query tables, query table
with no problems. Each of those entities are cleaned up properly. I store
the pointer to them and release them when I'm done but it seems as though
when the Refresh is called some unknown pointer is created that I'm unaware
of that I don't release thus causing the program to reside in memory after
quitting. If I comment out the call to Refresh all is fine - well except I
can't perform a query. That means the query tables, query table, range
passed to the query table for the connection, worksheet, worksheets,
workbook, workbooks, and application pointers are being cleaned up. Setting
the "BackgroundQuery" property to false didn't change anything.
What am I missing? Thanks.
-G-

I'm thinking this isn't possible... but..

Can you enter a named range from a cell value?

i.e.

Where DataA,CodeA and Cust are named ranges on another sheet.

Can I enter "DataA" in Cell A1, and "CodeA" in cell A2... to make the index read:

Obviously you cant do that as it will look in cells A1 and A2 instead of reading the text and going to that named range.  But
does anyone know if this is possible or another way?

The reason being I have a ~2000 lines of repeating data, and a different sheet that summarizes every 40 lines or so with Index lookups. If I could do something like this, I only need to enter the A1 and A2 values on each sheet and all my index formulas would be the same. Otherwise I'll have to change them each time, i.e. DataB, CodeB, DataC, CodeB in each row in each sheet!

Hi,

This code works perfectly but I don't want to include the very last row of data is it possible to adjust it to select columns A:AF rows 2: bottom row minus 1 row.

    With Range("A2", Cells(Rows.Count,
"A").End(xlUp))
    .Sort key1:=Range("A2"), order1:=xlAscending, header:=xlNo
    End With
Thanks

Excel 2002

If I set up an area, say c1:c5, all locked and then Allow users to edit
range and protect worksheet, I would expect an allowed user to be able
to double click on any cell in the range and input in it or move to one
of the allowed cells and input to it.

This isn't happening, double clicking on anywhere in the range defaults
to the first cell and you can only input there, doesn't seem to be any
way of moving to the other cells.

What am I doing wrong? ;-)

Regards Shaun

Hi Folks,

I've been building out a solution for my users which will allow them to
enter a date range, query a remote SQL DB via an ADO connection, and return
the results of to sheet1. I've made some great progress with the help of this
forum but I find myself perplexed as to why I do not return the correct
results when a user enters a single day as the date range for the query. For
example, If I enter 07/10/2006 for a starting date, and 07/11/2006 as an
ending date, the query returns the correct number of records for 07/10/2006.
I have verified this. But if the date range is 07/10/2006 and 07/10/2006 the
query returns an empty recordset. Below is the code I have so far...

' Define Input Date Parameters, Worksheet, and Integer object
Dim dtStartDate As String
Dim dtEndDate As String
Dim ws As Worksheet
Dim i As Integer
Dim iCount As Integer
Dim blnIsOk As Boolean

blnIsOk = False
Do Until blnIsOk
dtStartDate = InputBox("Enter a starting date for the report
range.", "Beginning Date Range", "XX/XX/XXXX")
If IsDate(dtStartDate) Then
GetDate = Format(CDate(dtStartDate), "mm/dd/yyyy")
blnIsOk = True
End If
Loop
blnIsOk = False
Do Until blnIsOk
dtEndDate = InputBox("Enter an end date for the report range.",
"Ending Date Range.", "XX/XX/XXXX")
If IsDate(dtEndDate) Then
GetDate = Format(CDate(dtEndDate), "mm/dd/yyyy")
blnIsOk = True
End If
Loop

' Create the connection object and query object
Dim dbConnection As Object
Dim strSQL As String
Set dbConnection = CreateObject("ADODB.Connection")

' Create the recordset object and initiate a new instance of it
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Sheet1.Range("A:D").ClearContents
Range("H6:H12").ClearContents
'Range("H7").ClearContents
'Range("H9").ClearContents

' Make the connection and run the query
dbConnection.Open "Driver={SQL Server}; Server=##;Database=##;Uid=##; Pwd=##;"
strSQL = "SELECT DISTINCT order_date,order_no,completed FROM oe_hdr WHERE
oe_hdr.order_date >= '" & dtStartDate & "' AND oe_hdr.order_date <= '" &
dtEndDate & "' ORDER BY order_no ASC"
'strSQL = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
BETWEEN '" & dtStartDate & "' AND '" & dtEndDate & "' ORDER BY order_no ASC"
dbConnection.Execute (strSQL)
rs.Open strSQL, dbConnection

For i = 0 To rs.Fields.Count - 1
Sheet1.Cells(1, i + 1).Value = rs.Fields(i).Name
Next
Sheet1.Range(Sheet1.Cells(1, 1), _
Sheet1.Cells(1, rs.Fields.Count)).Font.Bold = True
Sheet1.Range("A2").CopyFromRecordset rs

' Close the recordset object and release the memory space by setting the
object to nothing
rs.Close
Set rs = Nothing

' Close the connection object and release the memory space by setting the
object to nothing
dbConnection.Close
Set dbConnection = Nothing

As you can see I've tried using both >= and <=, and a BETWEEN in my query,
both with the same strange results.

Any advise is always greatly appreciated.

Thanks!

Hello,

Would highly like if you could shed some light on how I can accomplish this.

My sheet has three segments. The first being a selection for the users (the user can choose which category they want to view the reports for):
Selection
# Category Include?
1 Electricals Yes
2 Paper Yes
3 Food Yes
4 Beverages No
5 Pets No
6 Cosmetics No

The second segment has sales records

Sales
Year Month Category Quantity
2010 1 Electricals 5
2010 1 Paper 434
2010 2 Electricals 23
2010 2 Food 13
2010 3 Beverages 2323

And, the third has performance summary / report based on year-month against the selected criterions in the first segment

Summary / Report
Year Month Quantity
2010 1 ???
2010 2 ???
2010 3 ???

The max I've reached for the formula against Quantity is as follows:

=SUMPRODUCT(($C$16:$C$20=D26)*($D$16:$D$20=E26),$F$16:$F$20)

but, this doesnt consider the selection that the user has made, and as such the month-wise totals are coming up. I'm aware that I can use PivotTable with ease for this requirement, but I'd ideally prefer a non-PivotTable solution as there are many other dependencies.

Also, if the user-selected categories can be dynamically captured as a named range - could use it for several validation moving forward.

Thanks in advance!

Noaman

I'm looking to set up a dynamic range that spans the range C5:G20 on a sheet. Additional rows of data will be added hence the requirement for the dynamic range. I've established that I can create a dynamic range for one column (C) but I'm struggling to set this up for the required area of the spreadsheet. I'd be grateful of any help

Here's the code I have at the moment

ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:="=OFFSET(C5,0,0,COUNTA(C5:C200))"
Many thanks in advance

M

Hi,
I'm trying to create a file whose data changes depending on the drop down selected. This isn't a problem until I come to create a subtotal where I want to sumif or vlookup multiple criteria. I assumed that sumifs would be the answer but it seems to not work properly if I select the same criteria range repeatedly....
I've attached a simple example of what I mean.

The problem I have is that I am linking my excel file into a huge central excel file to pull out specific information and I am unable to amend the source data (it would be an easy fix if I could do this). I also do not want to create multiple worksheets and then total them up as this will make my file quite large itself - I'm essentially trying to create a reporting summary which can be used as a template and be adapted for other areas of the business.

Can anyone help with the sumifs or is there a better way of subtotalling without amending the base data or creating named ranges?

Thanks,

Wes

Hi,
I would like to kno how i can use a data range in one worksheet to look at a data in another worksheet,
The data in column A and B in worksheet named "PO Search" must remain matched together and then the Column B (BKN) must then look for (BKN) in column F of worksheet "Shipment report" and then also return the container size in Column b of the shipment report worksheet ?

I hope this makes sense, any assistance would be greatly appreciated !

Hi All,

When am trying to create the Pivot table from the Macro, am getting an error.

specifications:

The Macro has to select the Range from A5: CW and below is the macro code

Am getting the run time error of 1004


	VB:
	
 m() 
     
     ' Creates a PivotTable report from the table on Sheet1
     ' by using the PivotTableWizard method with the PivotFields
     ' method to specify the fields in the PivotTable.
    Dim objTable As PivotTable, objField As PivotField 
     
     
    Sheets("RD").Activate 
     
     
     ' Select the sheet and first cell of the table that contains the data.
    ActiveWorkbook.Sheets("RD").Select 
    DataRange = Selection.CurrentRegion.Address 
    Sheets("RD").Range("A5").Select 
     
     ' Create the PivotTable object based on the Employee data on Sheet1.
     'Set objTable = Sheet1.PivotTableWizard
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=DataRange, TableDestination:=" ..." 
     
     ' Specify row and column fields.
    Set objField = objTable.PivotFields("I1 B Contract No.") 

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


i would like to have a sql query (in vba) go against a range in the activesheet and return values (at first just for viewing and later an update query to let the user update columns in a given row(s) based on criteria in other columns of that row(s), etc.

thank you.


No luck finding an answer? You could always try Google.