Excel 2007 :: Posting Multiple Queries In VBA

Jan 8, 2014

I'm trying to create a sheet in Excel 2007 that organises shipments, the file is normally pretty large so I want to create a macro which does a few things, so when printed it's easier to work with. First one is to make a gap when items change in Column B and Column F, I can use the below code to make the gaps for one column but not both without making a separate macro. Is there a way to make the gaps on changes in columns B & F on one macro? (I have an example nearer the end)

Dim Rng As Range
Dim x As Long
Set Rng = Range("F12:F" & Range("F65536").End(xlUp).Row)
For x = Rng.Rows.Count To 2 Step -1
If Rng.Cells(x, 1).Offset(-1, 0).Value Rng.Cells(x, 1).Value Then
Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down
Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down
End If
Next x

After the data is separated I wanted to insert totals of the weights when there is a spacing, and a counter which stops after each spacing and restarts when the next items start.

Here is an example of the data I have before the Macro with Columns.

Column B
Column C
Column F
Column I

Destination of order
booking ref
Size
weight of orders

[Code] ........

This is how I would like the date to look like after the macro.

Column B
Column C
Column F
Column I

Count
Destination of order
booking ref
Size
weight of orders

[Code] ..........

View 4 Replies


ADVERTISEMENT

Choosing Between Multiple Access Queries In Excel

Jul 7, 2014

I have an excel sheet with a 'Home' and 'Data' page. I have imported data from an access query into the 'Data' page where I then push a button on the 'Home' page to run a macro on it. I have multiple queries in my database that I would like to be able to switch between in excel to run the macro on. Is this do-able without having to have multiple sheets?

View 1 Replies View Related

Posting Day Book Details And How To Enter Multiple Formulas In Single Cell

Apr 28, 2014

Problem-1) i want to round off the digit in the same cell i,e. Formula in F12 should be included in D12.

Problem-2) i have entered a sample account.in this i want to post the data entered in day book into their respective sheets of jaya and supervision automatically.

View 1 Replies View Related

Excel 2010 :: Calculating Value And Posting Total To 1 Of 4 Fields Dependent On Content Of Another

Jan 9, 2014

Excel ( 2010 ). I am creating a Sales Leads spreadsheet. Within the spreadsheet I have a the following relevant fields.

proposed sales value field ( F2 ), Estimated Close - which will be Q1,Q2,Q3 or Q4 ( G2 ), % Probability ( H2 ) & Q1 ( I2 ), Q2 ( J2 ), Q3 ( K2 ) & Q4 ( L2 ).

F2 = 150
G2 = Q2
H2 = 10%

I want to calculate the actual value of sales based on %prob and put the total into the correct field ( I,J,K or L ) based on what field G2 says. So in this case 15 into field J2. I have attached an example.

View 4 Replies View Related

Web Queries For Multiple Pages?

Dec 10, 2008

My question is about webqueries... specifically how to set up a query that will automatically fetch data on different pages (same data, but includes page 2, page 3, page 4, etc )

I've just figured out how to import a "new web query," but it seems like I won't be able to import 63 pages of it unless I do all of them by hand.

View 7 Replies View Related

Multiple Web Queries In One Column

Mar 20, 2014

I'm new to macro and have tried copy from others who have posted before but it don't seem to lead to anywhere.

I'm trying to automated a column of web query (sheet1,column A), a batch of listing site with no tables. with the results in sheet2.

Looping part as well. Also, is it possible to just extract <title> from the page source.

Here's what I have so far.

PHP Code: 

Sub Macro1()
'
' Macro1 Macro
'
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & Sheets("Sheet1").Range("A2") _
        , Destination:=Range("B2"))
 
[Code] ........

Tried changing the destination from

Destination:=Range("B2")) to Destination:=Range("Sheet2!$A$2"))

But keep getting error to change destination.

View 1 Replies View Related

Sort Multiple Web Queries

Nov 10, 2006

In short I have a yahoo query that loop through about fifty or so stock symbols. The query data is in column A to G starting with: Date, open,high,low ,close Adj. Now I have my Destination set up like so “Destination:= Range("A65536").End(xlUp).Offset(3))” for 3 row space between is retrieval

The problem is my: xlAscending

Selection.Sort Key1:=Range("A65536"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Ascending only convert the first query to Ascending the rest of the data remains Descending, how do I get all my query data to convert to Ascending using Date.

View 7 Replies View Related

Excel 2007 :: Copy Multiple Unique Values To Multiple Worksheets

Aug 26, 2013

I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?

View 1 Replies View Related

Automate Multiple Web Queries In One Worksheet

Jul 13, 2008

I can set up one query to take the parameters from say cell E1 and F1 and post the results of the query in cell A1.
The next query I want to take parameters from say cell E20 and F20 and post results in cell A20.
I need to run this several hundred times and am limited by the webite URL to 2 parameters.

I can't work out how to automaticaly change the URL via a macro so it adapts it with the changing parameters in my worksheet.

View 12 Replies View Related

Run Multiple Queries Macro Code

May 14, 2008

I am running 1,200 queries in succession in excel through a loop (visual basic). The problem is that, somewhere around loop 60, my computer grinds to a halt. I tried putting in a pause function and throwing in an autosave, to no avail. I think it has something to do with the memory, and somehow clearing it. Excel must be holding onto results from prior queries, and just runs out of memory after 60 or so.

View 4 Replies View Related

Multiple Web Queries Where Address For Each In Column

May 19, 2008

Lets say I have some web addresses in column A (Sheet1) and I want retrieve data from all those sites to another worksheet (Sheet2). Data from 1st site should be put to Sheet2!A1, from 2nd site to Sheet2!A51, from 3rd site to Sheet2!A101 etc.

There are some similarities with this thread: Dynamic Web Query From Cell Values, but I don't want the data to be on separate sheets and as my programming skills in Excel are rather limited, I failed to modify the solution given in there.

View 9 Replies View Related

Excel 2007 :: Multiple Lines In One Cell To Multiple Cells?

Mar 24, 2012

I have a column of cells with Customer info and address in each row, that I need to move into multiple columns. ie Cust name, acct #,address, city,state, zip the problem is ALL of the info for each customer is in ONE cell.To be clear, cell B1 has complete name address etc for one customer, B2 has the info for another, B3... on and on. To complicate matters as in the example, there is not a pattern in the lines. the first example shows the info beginning on the 2nd line of the cell, but the 2nd example shows info in the first line. I have tried the text to column fixed width. Didn't work because names and addresses are of varying lengths. Then I tried delimited, but there isn't a common separation between different data. I am using excel 2007. FYI I did go through many many threads and found some that were close to my problem but not close enough to work.

"
JOES TOWING SERVICE [0123451]
123 STREET

CALEDONIA FL 12345
"
"BUBBA / SHOP
ACCURATE AUTO SUPPLY INC [0234567]
21234 HYDRAULIC DR

LOCKWOOD OR 45678
"

View 7 Replies View Related

Run SQL Queries In Excel Against Database Using Macro?

Jan 29, 2014

I have sql queries in spreadsheet column and which needs to run against Db2 database daily and then update the result back to spreadsheet. This is tedious process and could there be a way to create a a macro using VB which performs the following?

1. connect to db2 database

2. take the sql query one at a time from every row and then run against database

3. Obtain the result and then update it back to last column of the spreadsheet.

View 3 Replies View Related

Report Design. Layout Multiple Database Queries

Jan 9, 2007

Is there a really good tutorial for laying out multiple database queries in excel? Or even just for automating reports between Excel and Access? Here is my situation: Monthly, Quarterly & Annually I report on performance standards such as: duration, best practices, cost-benefit analysis, etc. The information is pretty standard and is pulled from Excel Reports. Access is used to filter this information by appropriate month, quarter, year, and/or data type. In access I calculate the performance averages, max and min. Then this is in linked back to Excel to update the charts and summaries. My concern is how much time I spend rebuilding this system every month (about 4 business days)

View 3 Replies View Related

Connecting Excel To Access Queries - Named Ranges

Jan 11, 2014

So I have an excel workbook that is connected to an Access database I use. When the workbook opens, it refreshes the data from a couple queries in Access. However, every time it refreshes the data, it modifies several named ranges pulling from areas of those tables.

Example: $A$2:$A$50000 becomes $A$2:$A$50002 if there are two new lines entered into the table.

Because of this, some of the lookups and arrays I use are mismatching and giving errors. How to prevent the ranges from updating when the table refreshes?

View 3 Replies View Related

Import Text Dialogue/Wizard: Select File Once For Multiple Queries

Aug 7, 2006

I am importing a text file that has a lot more than 256 columns. The data will be updated weekly with a different text file. I know how to get past the 256 column limit when importing a text file like this. This link explains the method I am using to import the text file. [url] Since I am importing onto several sheets I get the dialogue box asking me to choose a file to import from for every sheet when I use RefreshAll. The same file that is used by the first sheet will be used by the rest of the sheets. Each sheet just imports a different section of columns from the text file. I want to avoid choosing the same file 30 plus times (once for each sheet) when I click refresh all. Is there a code that would allow me to choose the file for the first sheet, and the other sheets would follow suite?

View 9 Replies View Related

Excel 2007 :: Multiple Value With Criteria

Oct 6, 2013

I have use excel 2007. In our sheet i have in cell "B3" product name i.e. "abc" , "xyz" etc. That is called sales of goods.

In cell "C3" i have number of quantity
If "B3" is "abc" then only calculate below :

1st option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=15 & cell "c3" is<19 then message shown as "one mobile free"

2nd option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=20 & cell "c3" is<39 then message shown as "two mobile free"

3rd option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=40 then message shown as "three mobile free"

In view of the above which formula applicable.

View 4 Replies View Related

Excel 2007 :: Multiple IF Statements Within One Cell?

Feb 13, 2014

I am using Excel 2007 and am attempting to write multiple IF statements within a cell. This multi-cell validation would be used to validate that A=B and C=D but A&B do not equal C&D as illustrated below:

ABCD
1111FALSE
1110FALSE
1101FALSE
1011FALSE
0111FALSE
1100TRUE
0110FALSE
0011TRUE
1001FALSE
1010FALSE
0101FALSE
1000FALSE
0100FALSE
0010FALSE
0001FALSE
0000FALSE

Here's the formula that I attempted to use:

=IF(A2=B2, (IF(C2=D2), (IF(A2=C2), "Error3", "OK"), "Error2"), "Error1")

However, I receive an error message stating "The formula you typed contains an error" and it highlights the D in the equation.

I am not sure why the D was flagged as an error. If anything, I thought the "Error" portions would have been flagged...

Is it possible to do a multi-cell validation in Excel?

View 6 Replies View Related

Excel 2007 :: How To Do Multiple Hyperlinks In A Column

Jul 15, 2014

I have Excel 2007, and I have column 1 with a thousand entries "HC 14-0001" all the way down to "HC 14-1000". I have on my hard drive 1000 foldiers named the exact same "HC 14-0001 to HC 14-1000". I want to create a Hyperlink from each item in that column to its corrisponding folder on the hard drive. I can easily create the Hyperlink, but I want to find someway to copy them. I can create the first two rows... HC 14-0001 and HC 14-0002 and create good Hyperlinks to the folders, but when I highlight them and drag them down, the excel spreedsheet is updated all the way to HC 14-1000, but the Hyperlinks do not change.

How can I get the Hyperlinks to change? I don't want to spend the next week creating Hyperlinks for a 1000 entries on this spread sheet, and I have 5 other spreadsheets exactly the same.

View 13 Replies View Related

Excel 2007 :: Vlookup Using Multiple Columns?

Jun 27, 2011

I'm looking to simply find a function that allows me to lookup a certain value in a table of data and return the value in a specific column. So if I looked up the current date, 6/27/2011, it would return the week it is in (26). I dont want to be limited to just the monday column however, as the dates I will be throughout the entire week. Is there an easy way to do this in excel 2007?

MondayTuesdayWednesday ThursdayFridaySaturdayWeek
5/2/20115/3/20115/4/20115/5/20115/6/20115/7/201118
5/9/20115/10/20115/11/20115/12/20115/13/20115/14/201119
5/16/20115/17/20115/18/20115/19/20115/20/20115/21/201120
5/23/20115/24/20115/25/20115/26/20115/27/20115/28/201121
5/30/20115/31/20116/1/20116/2/20116/3/20116/4/201122
6/6/20116/7/20116/8/20116/9/20116/10/20116/11/201123
6/13/20116/14/20116/15/20116/16/20116/17/20116/18/201124
6/20/20116/21/20116/22/20116/23/20116/24/20116/25/201125
6/27/20116/28/20116/29/20116/30/20117/1/20117/2/201126
7/4/20117/5/20117/6/20117/7/20117/8/20117/9/201127
7/11/20117/12/20117/13/20117/14/20117/15/20117/16/201128
7/18/20117/19/20117/20/20117/21/20117/22/20117/23/201129
7/25/20117/26/20117/27/20117/28/20117/29/20117/30/201130
8/1/20118/2/20118/3/20118/4/20118/5/20118/6/201131
8/8/20118/9/20118/10/20118/11/20118/12/20118/13/201132
8/15/20118/16/20118/17/20118/18/20118/19/20118/20/201133
8/22/20118/23/20118/24/20118/25/20118/26/20118/27/201134
8/29/20118/30/20118/31/20119/1/20119/2/20119/3/201135
9/5/20119/6/20119/7/20119/8/20119/9/20119/10/201136
9/12/20119/13/20119/14/20119/15/20119/16/20119/17/201137

View 4 Replies View Related

Excel 2007 :: Combine Multiple Rows Into One

Jan 10, 2012

I have a list of about 4,000 contacts each with about 2 or 3 products with us. Each product creates a new contact row.

I would like to merge each row with the same contact into one row.

I have attached an example : Example test.xlsx‎

View 14 Replies View Related

Excel 2007 :: VLookup Across Multiple Columns

Feb 20, 2012

I am using Windows xp and Excel 2007. I'm creating a user form and have a vlookup formula that goes to a table and looks up calories and sodium based on the text entered by the user. My problem is that I want to be able to populate the 3 columns with information and then sum the values that are returned to the Calories and Sodium columns. Is that possible with Vlookup?

=IF(ISNA(VLOOKUP(F8&G8&H8,Food!A:E,4,FALSE)),0,VLOOKUP(F8&G8&H8,Food!A:E,4,FALSE))

Water 24 ozOther DrinksGelsSolid FoodCaloriesSodium

00
00
00

View 7 Replies View Related

Excel 2007 :: Insert Multiple Objects

Jul 4, 2012

Excel 2007.

Insert - Object allows me to insert a object (display as icon is used). Is it also possible to insert multiple objects in one go? Via this option I can always only select 1 object.

View 1 Replies View Related

Excel 2007 :: Multiple VLookup In One Cell?

Nov 27, 2013

I am trying to get one cell to look at different sheets. if the first sheet doesn't have a value, than look in the next sheet. current formula is

=IF(ISNA(VLOOKUP(Q2,'sheet2'!$D$2:$K$1807,7,FALSE)),"",VLOOKUP(Q2,sheet2'!$D$2:$K$1807,7,FALSE))

I am having cell return blank if no value is found. it is currently looking only at one sheet.

I don't want it to combine the values like this formula does.
=(VLOOKUP(Q2,'sheet3'!$D$2:$K$200,7,FALSE)&","&VLOOKUP(Q24,'sheet2'!$D$2:$K$1807,7,FALSE)) #.###,##.##

sheet 1 is where the cell I am writing the formula is in, and I want to look in sheet 2, 3, and possible 4 for a value, I want to only have the greatest value show between the sheets. is this possible.

I am using excel2007.

View 9 Replies View Related

Excel 2007 :: List Multiple Folder Horizontally

Aug 1, 2013

I am using excel 2007. I have multiple folder with multiple files inside like below

folder1
file1
file2
file2

folder2
file1
file2
file3

folder3
file1
file2
file3

i need output like

folder1 file1 file2 file3
folder2 file1 file2 file3
folder3 file1 file2 file3

I need to use a function like =filelist(a1) or =filelist("folder1") then it should list the files horizontally like above.

View 6 Replies View Related

Excel 2007 :: Variable References To Multiple Workbooks?

Jan 10, 2012

i make a lot of "trending" files at work where i link a cell to a seperate workbook where all workbooks are in the same folder.

these workbooks all have the same name with the date at the end

example:
workbook 06-01-2011
workbook 06-02-2011
all the way to
workbook 12-31-2011

usually i use find replace to change the date one at a time, but doing that 365 (x how many cells i have referencing) times is redundant

i have seein INDIRECT of the cell changes but nothing for the workbook name chaning

here is what i am trying to do:
='C:File Path[WorkbookX.xls]Sheet1'!A1
where the X WorkbookX is variable (the date at end of file name)

i have a list of all workbook names or just the dates and would like to do something like:

=D1&$A$1
where D1 is the full path name
or
='C:File Path[Workbook"D1".xls]Sheet1'!A1
where D1 is the date name

using excel 07 i prefer not to use a macro for how i am doing this but if a macro is the only way then i cannot have it actually open the files to pull the data. i prefer no macro since i set it up for future dates so when the new file is created it automatically updates the reference.

my end result i usually have something like A1=referenced cell from first workbook, A2= cell from 2nd book, etc. sometimes B1 would = a different cell from first workbook and so on if i am trending multiple cells from the workbooks

View 1 Replies View Related

Excel 2007 :: Consolidate Multiple Loop Statements

Jan 29, 2012

I'm working in XL07 on Win7.

The workbook has two worksheets "Data" & "Crunch". "Data" contains the raw data-range B1:U50. "Crunch" is the processing worksheet. Row 1 is a header row. Columns A-D are empty. Column E (rows 2-81) contain the numbers 1-80.

The macro is intended to run in "Crunch". I need to add the CountIf function in every cell/row filling the range F2:BC81. A loop does the trick for me to fill the rows in each column but I don't know how to move the loop down each column without creating a new loop.

A section of the code is below. Notice a majority of the code is the same, the copy in red is variable from loop to loop. I need to repeat this loop through column "BC" (50 times in total). My guess is there is a way to write this code one time instead of 50 times adjusting the variable components.

Code:
'Find total # of records and then store in variable
totalrecords = ActiveSheet.UsedRange.Rows.Count
'CountIf Statement

[Code]....

View 3 Replies View Related

Excel 2007 :: Combining Data With Multiple Row Entries

Mar 7, 2012

I'm using Xl 07 and need to combine two sheets of contact info and have them sorted alphabetically.

Each contact entry takes the follwing form(comma's denote new columns)

55555, Name , John, Smith, Tel: , 555-555-5555
ID , Company, XXX , , Email, xxxxxx

Each sheet has a list of 200 or so entries about 400 rows long. The entries on each sheet are sorted in alphabetical order. the names also alternate alphabetically from sheet to sheet. So when i look at the entries on my final sheet, the 1st will be from sheet1 the second from sheet2 the third from sheet1 and the fourth from sheet2 etc.

I'm not sure whether I should build a new list by pulling alternating entries from each sheet or just copy one list below the other and then try to sort it somehow.

View 1 Replies View Related

Excel 2007 :: Multiple Column Dropdown List?

Apr 15, 2012

I am trying to make an excel sheet which has a 2 column drop down. For example I want to have a drop down list in column 1 and when I select "Item A" in column 2 "item A" 's phone number appears. If i select "item B" form the drop down this, "Item B's" phone number would also appear in column 2 and so on. how this is done (if possible) on Excel 2007? I was hoping it could be done from one workbook to another but If it is easier from one worksheet to another then that is fine too.

View 5 Replies View Related

Excel 2007 :: Conditional Formatting On Multiple Cells?

Apr 17, 2012

I'm using 2007 and want to add conditional formatting to multiple cells.

Say I have 3 columns and 10 rows of data (A1:10 ; B1:10 ; C1:10). I want to add an icon set to everything in columns B and C based on a comparison with the figure on their immeidate left. So B1 is compared to A1, C1 to B1 etc.

When I do it though it compares everything to A1 (formula in the Conditional Formating is =$A$1 and it won't let me remove the $'s). It looks to me like I can't do it and have to add the formatting to every cell indiviually (which is quite a lot).

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved