Attached is my .xls with dummy data Fetch data By Director.xlsx.
My intention is that once the Director and Month in tab 'By Director & Month' is chosen and the Fetch Data commandbutton is clicked, a new workbook should open.
This new work book should have :
all entries in tab 'Monthly Invoice Input' for that Director-Month combination,along with a sum total of column Q(Total invoice amount) in the last row.
I am trying to create a macro for vlookup. Each time the name of the source file changes. I dont want to update the formula everytime. So Iam trying to do a macro which will fetch the data from the file that the user mentions in the sheet2.
I have attached the source file and the VLookup file. Now when I enter the source file path in sheet 2 of workbook "Vlookup", and then if I run the macro it should open the file and put the dates in Sheet1 (Columns "D" and "E") of workbook "Vlookup". The dates in the source file will be available in sheet 1(column F and H) of that workbook.
I have created a macro file which will fetch the data from sql server. Here is the code
f Me.Cmbchoose.Value = "" Then
MsgBox "Please Choose From the drop down Menu!!", vbExclamation
Exit Sub
End If
If Me.Cmbchoose.Value = "Merchants Reports" Then
Dim oConn As ADODB.Connection
[Code] .....
The code is working fine. Now my question is that I want to access it from my home. How do I do that without DSN set up?
I have a data which i need to total based on document types.
Document types in cells are AB=CELL A1, ZR=CELLL A2, ZP=CELL A3,
SK= CELL A4.And amounts in rows 555=CELL A1, 625 = CELL A2,
725= CELL A3, 925=CELL A4 and so on.
My challenge is to get all the total of ZR,SK,AB type of documents using macro.Initially i used this formula, however now document types are more than one.
=SUMIF([Book1]Sheet1!$C$2:$D$4,"zr",[Book1]Sheet1!$D$2:$D$4)
We're working with a large workbook with data from A5:P20000 on sheet1.
I need to be able to pull data from sheet1 to sheet2 based on the invoice number in column A on sheet2.
Sheet2 will have different invoice numbers in column A, some of these invoices numbers are in column H on sheet1. I need excel to find these invoice numbers and copy the data from that row on sheet2 to the row with that invoice number on column2.
Example:
Sheet2 A5 has invoice number 1234457
This same invoice number is located on sheet1 H3400
Need to copy to sheet2 (starting in cell B5) the following cells from sheet1 (B3400, C3400, E3400, G3400, H3400, I3400, J3400, K3400, M3400, N3400).
Attach is a sample of my project. I have to loop through B9:B80 in sheet("Raw Data") and capture the value of each cell. EG:
* if Value captured is 120A --> check with sheet("SP14F") from the row which contains the same value.
* i have to check that the aperture, outer and inner values of Layer 120A and so on..of the Raw Data sheet is the same as that of in the SP14F sheet.
* if the values are not the same, change the font to red
i understand that this task requires looping and arrays.
I have a list of several ranges defined in vba, and i am trying to find which range a cell containing word is in excel. Im am looking for help for a good way of doing this.
For an example: Say I have 2 ranges defined, the first range corresponds to column A, the second to column B (just an example). The word "Dog" is written in cell B15. I want to find out in vba what range "Dog" is in. I would like some vba code to check and find either if it in a given range (boolean) or a way of checking a list of ranges and returning which it is in. The word will only appear once on the spreadsheet. I have played around with the find command (A=range.find(Pl), then check if A exists) but i havent had any luck (I get an 'Object variable or With block variable not set) I tried searching for a while, but was having trouble finding something related to what i am trying to do.
I need to fetch corresponding data value from a data set which is spread across the sheet horizontally as well vertically. i need the net sales and net profit figures to fetched to a different worksheet/book. i have quarterly data horizontally and yearly data vertically. Below is a table in a horizontal manner. How to automate or less-time-consuming
Output required as:
period 6-30-2000 9-30-2000
net sales ........ ............
net profit ....... ............
Type
Un-Audited
Date Begin
4-1-2000
Date End
6-30-2000
[Code] ......
I have a sheet which has certain formulas & charts. I have a folder which has several workbooks of data.
Generally, I open the folder, open one workbook, copy the data, paste it to my Master Workbook, and based on this data, my charts update.
Now, the problem is that, I have to open every workbook one by one, copy and paste it to the master.
I have not less then 210 workbooks. I just want to know if it is possible to open the folder, open the desired workbook, copy the specific data and past it to master workbook, FROM Maste workbook itself.
Fetch data from the table on the page given by the link below..
http://www.moneycontrol.com/stocks/f...l&sort_order=0
I tried to use the following code to get data from an ASPX webpage:
[URL]....
VB:
Sub Fetch_Marcas()
Dim IeTbl As Object
Dim obj As Object
Dim Scrn As String
Dim objIE As SHDocVw.InternetExplorer 'Microsoft Internet controls (shdocvw.dll)
[Code]....
However, I cannot get past the point where I click over the register (highlighted in yellow in Picture 1), to get the data displayed at Picture 2.
Picture 1
Picture 1.jpg
Picture 2
Picture 2.jpg
Please note that this query requires to click over what is not a button (I looked in the forum, but no similar question was made before on this topic).
Is there a way to capture the records(which meets the criteria) from different excel sheets' sheet1 (A.xls,B.xls) located in some path (c: est) and copied them into our own Result.xls spread sheet?
It would be great and very useful, if you could provide the solution for this.
I would like to be able to have Excel VBA pull down the data from the following website.
The first URL will take you to the website, but you then need to select a state shown in bold from the Radio side of the index window in the middle of the screen. Once at the next screen, this is the table I wish to download into excel.
I want to do this for all the states listed. How can I do this or is it possible since you have to select a state prior to the listing.
HTML Code: [URL] ..... I found this code from anothe thread but it did not work.
Code:
Sub GetData()
Dim x As Long, y As Long, t As Long
Dim htm As Object
[Code] ....
So the problem is that I have two workbooks: one has a set of identifying values which are a subset of one of the sets of values in the second and I need to match them up. In addition I need to take the values two columns to the right of the matched values in the second workbook and put them in the first.
Here is what my formula looks like now.
=IF(A5=VLOOKUP(A5,'[WBOOK2]Sheet1'!$A:$A,1,TRUE),INDEX('[WBOOK2]Sheet1'!,MATCH(A5,'[WBOOK2]Sheet1'!$A:$A,0),3),"NF")
Whenever I try to run it an error message comes up and highlights the match function name.
I want some code on how to fetch data from an Excel Database and display it on respective textbox.
Like I have these coloum name in excel sheet; Emp Id
Name
DOB
Dept
Reporting to
Transport req.
I want to have VBA code to fetch it on txtbx1, txybx2 and so on.
I am trying to open set of excel files to fetch data starting from a master excel file where the links are given using a loop. Some of files given as links are either absent or the link is wrong.
* What is the syntax to find if the link is correct/present
* What is the syntax to find if the file is present in specified location
* What is the syntax to find if the file is password protected to open
* I use error handler to resume the next statement if OPEN statement fails.It works fine inside the loop only for first two files.
For example if the third link is errorneous the error handler fails and RUN TIME ERROR occurs.
I have ahseet with the following columns:
Col 1 = Item Description
Col2 = Price
Col3 = Discount Rate
I would like to gather a count on another sheet of all instances in col 1 where it is equal to 'Fridge' I know I can use the SUMIF function but I would like to do it in VBA code.
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
I have a table of data with which I need a macro to clear a range of data based on certain criteria. I have attached a file as an example. In this example the current period is 6. I therefore need to clear columns which have a period of 7 or higher.
Essentially, if the current period is x, then clear the range in the period columns if the period is greater than x.
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do.
Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest.
Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN.
There could be 100 or 1000 transactions generated based on the day.
I would like a simple macro that would actually move a cell based on criteria. In my case it would be: In row A, if a cell starts with 'Agent Name' then that cell needs to move down one cell replacing the contents of that cell.
View 3 Replies View RelatedI have a file, with this dummy data
Fruit Country QTy
Apple India 100
Apple USA 200
Orange India 150
Grapes Aus 100
Grapes India 100
I need to macro, wherby it will create 3 files namely apple, orange,grapes and store the relevant rows.
Hence I need a macro to split file into different files based on different values in a column and accordingly name it .
Column DColumn EColumn FTarget %DifferenceCash4%5.12%Large-Cap8.5%1.73%Commodities4%-1%
I need to color code Col F based on the following criteria:
If F2/E2>20%, then shade pink else no color. I only care about the absolute values. The 20% number can change depending on the category. So for commodities, the limitation is 30%. Like that I have a list of about 20 asset classes, 14 of which have the 20% constraint, rest are either 25% or 30%. I would like the cell color to be updated each time I import a file. Also I have other conditional formatting on Col F (like red color if negative number) so I don't know if another conditional format will work on the same cells?
Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
View 9 Replies View RelatedA macro is required to identify rows within a selection e.g. entire column A, that share the same value, then delete appropriate rows depending on the values in another column. The attached example details the requirements.
Extract Rec1.xlsx
I need a macro to delete old data from a large selection of data, in order to keep the size down.
What I want this macro to do is
Check all rows from 5 downwards.
If A5 (date) is less than cell $B$1 AND B5 is not equal to C5 then delete the whole row.
Continue until reaching the bottom.
This is what I am trying to achieve:
1) Prompt user to select a file (or multiple files)
2) Copy the files that meet certain criteria to a folder
My attempt (fail):
VB:
Sub FILES2SFTP() Dim FileNames As Variant Dim I As Integer Dim fso As Variant Dim Data As String ChDrive "G:" ChDir "G:TEST" Data = InputBox("Enter the date", "Enter the date", Format(Application.WorksheetFunction.WorkDay(Date, -1), "yyyymmdd")) Set fso = [code]....
I get error 424 object not found in this line:
If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then
I would like to hide rows for data that does not meet specific criteria. For example: If a user selects room number 101 from a drop down in B1, I want to filter data in range A3:F1000 to show me only room 101 rows (A column) where C column contains a value larger than zero OR D column contains a value larger than zero OR E column contains a value larger than zero. I do not want it to return rows where 101 may be in other columns beside A.
Sample:
A B C D E F
__________________________________
1 ROOM: 101
2
3
4 101 XX 1.2 0 0 P
16 101 YA 0 0 1.1 L
23 101 JJ 3.2 2.1 0 L
55 101 JJ 0 0 1 P
So, if a row contains 101 in column A and all three values in columns C, D, and E equal 0, then those rows will be hidden.
I want to compare 2 lists in separate sheets based on multiple criteria and delete the duplicates
Sheet 1 - new list in column A:E
Sheet 2 - old list in columns B:F
So here is what I need: the macro should generate single IDs made of Sheet 1 Ai,Bi,Ci,Di,Ei cells for each row i to the end of the list + generate single IDs made of Sheet 2 Bi,Ci,Di,Fi
If . Evaluate (Sumproduct (IDs made of Ai,Bi,Ci,Di,Ei from sheet1) & Sumproduct IDs made of Bi,Ci,Di,Ei,Fi from sheet2) >1 then delete the entire row in Sheet 2.
This will leave me with only updated items (rows) in Sheet 2
Horses have 5 running styles; early speed to late closers; denoted as 1 to 5 in column Z. I'd like to count those noted as 1 thru 3 for each race.
In column FC (the last column with data) I have a race ID # for each race on the spreadsheet, somewhere between 2500 to 5000 races in each one. They're a monthly record. The race ID # is a concatenation of columns B (Track ID), C ( Date) & D (race #). Each is unique to its race. E.G. Aqueduct, jan 1st, 2004, 2nd race is id'd as AQU379872.
Would it be possible to create a macro that would (A) count the number of horse 1s, horse 2s & horse 3s from a specific race ID & then post those numbers in columns FD thru FF & (B) then continue to the next race ID in column FC & count those horses from that race & so on thru the entire spreadsheet?
Would it be simpler if each race ID were changed to a number--race 1 down to race 2500?