Search results

If I type the formula in myself it should look like this: =INDEX('[Any AR 03.01.13 PM.xls]Sheet1'!($D$3:$D170,MATCH(D4,'[Any AR 03.01.13 PM.xls]Sheet1'!($H$3:$H170,0)) But VBA writes it like this: =INDEX('[C:Temp[Any AR 03.01.13 PM.xls]Sheet1]Any AR 03.01.13...

First of all thamks for the help. Hui, I tried both your suggestions and get a Compile Error Invalid Character on the $ in $D$3:$D shrivallabha I get a Run-Time Error 1004 with yours. I have lastRow1 as Long in the top half of the macro. This code somewhat works but when you look at...

I get invalid character message in the formula where I have $D$3:$D$ and $H$3:$H$. Any thoughts? worksheets("sheet1").range("E3").Formula =_ "=index([filename.xls]sheet1!("$D3:$D$" & lastrow1, " & "_ match("D4"," &...

Let me ask you this Luke, How about INDEX and MATCH? Would that be easier to do?

Thanks Luke! I had to change a few other things but my issue is still with the VLOOKUP. Worksheets("Sheet1") is the active workbook and Filename is open. The reason the macro uses Filename is because this workbook will always have a different name. The part that seems to be the issue is "...

Just to be more clear: Lookup_value = Any AR Cell E3 Table_array = CHOOSE({2,1},macro opened workbook sheet1 Range $D$3:lastRow1 and $H$3:lastRow1 Col_Index = 2 Range_lookup = 0

OK I solved the problem I had above. I had the database just keep the file name the same when it exports to Excel and the user will change the file name when they save the file. Now here is what I have: Database File is open, the file name is "Any AR". And the file the macro opened with...

BTW, the macro listed above is in its own workbook and is doing work in the other two workbooks.

Here is the situation. I have a report that I run from an external database. The report will always have a different name due to when it is run (several times a day), 03-21-13 AM1. When it is run it automatically opens in Excel 2007. I then open the last report in a folder for which I have VBA...

I had a post where I wanted to suppress either 0's or #N/A's in pie chart and keep them from showing up in the legend. My next issue was how do I do this in a bar chart? Scenario: My titles are in column A and values in coulmn B starting in cell A1. Item A 8 Item B 0 Item C 9 Item...

Luke, I figured it out, it is all in how you show the data labels. You have to delete the legend choose Label Options for each series then check Series Name and Value. By doing this it does not dhow the #N/A's. Thanks, Mike

I ended up keeping the pie chart. The stacked bar chart still plotted the #N/A's. I used data validation to create a dropdown list of months. I then put in two helper columns: Helper A: Based on the month it pulls the data and assigns the value, blank, or #N/A =IF(J3>0,B3,"")...

I have a spreadsheet with data to track errors. Not all categories will have an error each month. Example: Items are in column A, Jan column B, Feb column C and, Mar column D Jan Feb Mar Item A 8 7 0 Item B 0 6 2 Item C 4 6 5 Item D 0 2 0 Item E...

I have solved it. Anyone who wants to autorun a macro can set it up in Schedule Task in Control Panel using the following code and save it using NotePad and RunMacro.vbs: Option Explicit Dim xlApp, xlBook Set xlApp = CreateObject("Excel.Application") '~~> Change Path here to your...

Happy New Year Everyone! I am trying to run a macro through Task Scheduler. I have the following VBS file (in NotePad) saved as *.vbs and it errors out: 'Write Sheet's full path here strPath = "C:General UseCopy Daily Files.xlsm" 'Write the macro name including module strMacro =...

Luke, I do not need the sum of TWG for all sheets combined. Each sheet in the workbook represents a department, say Dept 100, Dept 200 and so on. So I have to add TWS and TWG for each department individually. This goes into a report so management can review each departments expenses. Clearer?

OK, finally I found some code that does most of what I want. So, how do I get it to extract the "Total G&A" part and add it to "Total Wages & Salaries"? Here is the code: Sub Extract() ' Object variables Dim wks As Excel.Worksheet Dim rCell As Excel.Range Dim fFirst As String &#39...

Hello all, I'm rather new to VBA (Excel 2010) which is why I'm asking this question. I have a workbook that contains many worksheets within the workbook. In column A of all the worksheets I am looking for "Total Wages & Salaries" aka TWS and "Total General & Administrative" aka TGA. Ideally...

Works perfect! Thanks a bunch and for your time.

I have a database (Access) that contains 64 columns of data. I am only interested in pulling 14 of those columns into Excel. My code works to pull all the data into. So, how do I choose only the columns of data that I want? Here is the code: Sub Import_AccessData() Dim cnt As...

Thanks Deb & mercatog! I had Deb's code but did not have the "> 0" So close but so far away. Again, many thanks!

I have a UserForm with some CheckBoxes and TextBoxes. If the CheckBox is checked my macro assigns a value such as "Cat" to a new workbook then saves the workbook in cells D15:D18. I can import the data back into the UserForm in the TextBoxes. My problem is how do I recheck the CheckBoxes? I...

Deb, It works perfect! I had the second suggestion but was using it in the wrong place. Thanks so much for everyone's help! Mike

Luke, If I input 1002 my message box returns "COID Not Found!" If I input 1001 the TextBoxes are populated properly. It is like only row 2 is selected instead of looking all of column A of Sheet3. Deb, They are the same tbCOID is in frmSales and the rest of the TextBoxes. I have...

I have a UserForm, I enter a ID number into a TextBox called tbCOID. I am trying to use the Find method to look for the value in tbCOID and have it populate other TextBoxes. The data I'm trying to retrieve is stored in Sheet3 of the Workbook that has the macro. The code only returns the values...