Each week I put together a list of changes to a pre-existing database and then send off those changes to another group. Later in the week when they return the updated file I check to see if they have made all correct changes. These changes generally include adding or removing text from specific cells or repositioning columns.

I am looking for a dynamic way to track if all necessary corrections were made. To be clear, I do not need simple conditional formatting which will tell me which cells are not alike. I know which cells are not alike. I need to be able to say in some form of coding:

"Check to see if cell X is the same as cell Y, minus the highlighted/colored/striked-out text." Or similarly "Check to see if cell X is the same as cell Y, except with the addition of the text that was colored a specific color in cell Y."

Any advice would be greatly appreciated.

Hello

I have a series of worksheets that represent monthly client statements. I ultimately want to be able to send the statements individually or in mass at month end to clients via MSExpress. Having never delved into Macros I am a complete novice and therefore need to be treated with kid gloves.

The worksheets are identical in layout and only differ with user data. I have created cells per worksheet that refer to a 'Master' worksheet to mange statement dating, period, year etc. Also I have a unique 'account number' per worksheet. I want to be able to save the individual worksheets as .pdf documents, creating the path and file name from the existing path and atributes within the file. This I have done as follows.

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&K7&"_"&K4&J4&".pdf"

which results in a string as follows

C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf

All goes well when recording the macro below and it appears to work well, that is until I run it on any of the worksheets that it was not recorded in.

My problem is that the .pdf file name is not updating to the new worksheet data and retains the original file name. I have looked at some comments on the www, however, only being a geek wannabee I have yet to acquire the skill-set to understand whether my problem is unique.

I don't think it has anything to do with the .pdf creator, more like a refreshing or waiting period for excel to catch up with itself. As you can see from the macro I do go to the lengths of copying the result of the formula string as a 'value' into a secondary cell in an attempt to force the calculation to reveal the updated file name, but with no luck. I am confident that there is a very simple solution, perhaps this is why it evades me.

Any assistance will be welcome.

Sub Save_pdf()
'
' Save_pdf Macro
'

'
Range("Q1").Select
Selection.Copy
Range("R1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("R1").Select
Application.CutCopyMode = False
Range("A23").Select
Application.CutCopyMode = False
Range("R2").Select
ActiveCell.FormulaR1C1 = _
"C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf"
Range("A23").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub

I am trying to create a conditional report. For example....if column E is "D" then what is displayed in column G will be "Account Requiring Adjustment". However, I do not know if the 'IF' function is the way to go or if the 'LOOKUP' function is the way to go. I've tried both, but with no success whatsoever. I am hoping someone may be able to help. The formula's I've created appear as follows:

IF FUNCTION:
=IF(E2=D,"Account Requiring Assist Code Adjustment",IF(E2=E,"Adjust Source Code",IF(E2=F,"Adjust LM Counselor",IF(E2=G,"Adjust Plan Type",IF(E2=H,"Email Assigned HOST or Negotiator")))))

LOOKUP FUNCTION:
=LOOKUP(E2,{E,E,F,G,H},{"Account Requiring Assist Code Adjustment","Adjust Source Code","Adjust LM Counselor","Adjust Plan Type","Email Assigned HOST or Negotiator"})

Morning All,

I have a Cash sheet with totals on it but I can not figure something out.

When entering £1.50 into K6, L6 shows £1.50
When entering £2.00 into K7, L7 shows £3.50 and so on, but I do not want to see £3.50 in all the cells in L but when I enter figures into the other cell I want the L column to change to the new calculation and so on.

L39 will show the sum of column K. I hope this makes sence, I am not very good at explaining things. I have attached a copy of the cash sheet to hopefuly explain it self.

Shazz
xx

Attached Files

• DTD Cash Sheet.xls (35.0 KB)

I need urgent help to replace a pivot that I have created for reporting
The data sheet has more than 20,000 rows and 65 columns. The main information forming this reports come from 4 columns - e.g Classification, Sub Classification, Category & Count
Report needs to populate Top 5 Records from each subclassification with Category name and Count
e.g.
Classification Sub Classification Category Count

Apple Golden Delicious Fresh 50,000
2days-old 23,000
Week-old 20,000
Unclassified 10,000

Pink Lady 2days-old 30,000
Week-old 20,000
Fresh 2,000
Unclassified 10,000

Orange Navel Fresh 45,000
2days-old 32,000
Week-old 20,000
..... & so on

The formula is required to populate top 5 records at the right. The classification and Subclassification can remain fixed in the report table in a new Tab as that would not change quite often, hence the formula to populate Column 3 & 4 in the example above is desparately needed.
Any Help is really apprecaited.

Hi I am doing calculations of number of reducers i.e 10. However evertime I do calc for the next reducer, I will have to copy and paste to the table with reducer number (see attached workbook). Recently I had to work with about 80 reducers and this is some what cumbersome.

All want to know is there a way I can input data on the green shaded areas and change it as per reducer size and excel will copy the required data to corresponding reducer number automatically.

Reducer 1, D= 1500mm, d1=1200mm and calculated x=1050mm
Reducer 2, D= 1000mm, d1=900mm and calculated x=350mm
Reducer 3, D= 1000mm, d1=800mm and calculated x=700mm

Your help will be appreciated

Attached Files

• Reducer.xlsx (25.2 KB)

Hi,

Can anyone please help to find a formula for this case?

This is a table of shared monthly fee for a rented house. I can't make a proper formula (from C8 to C11 cells) which will calculate the rent fee for each student according to stayed days and share among them (B5) respectively.

Pre-conditions:
-Monthly rent fee is always fixed;
-Daily fee varies according to current month;
-A student who didn't stay a single day should be excluded from the list (in this example there are 4 student, but Tom is excluded in B5).


Thank you in advance.

Hello,

I want to find out the Average, Median Max and Min of the "Note Size" (Column B) if the Enterprise Value (Column A) is within the following range:

All Sizes
<$25M
>$25M - <=$100M
>$100M - <=$250M
>$250M - <=$750M
>$750M?Excel Question.XLSX

i tried doing the MEDIAN(IF(A2:A1000<25000000,B2:B1000) as an array but I numbers don't look right.

I have attached the file for your to look at (two tabs), please let me know what I am doing wrong.

Thank You!

Attached Files

• Excel Question.XLSX (12.2 KB)

I am getting this error -2147467259 Unexpected Error from external database driver when running a vba macro code at this line-.Provider = "Microsoft.ACE.OLEDB.12.0"
in below code-

Code:

---

MyConn = _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=Excel 12.0;"

---

This is a simple vba macro (not using .net,sql server or ms access). Only excel is used. This works fine in following combinations- Excel 2007 & Windows Xp, Excel 2007 & Windows 7. But in Windows 7 and Excel 2010 it gives this error if the user is not admin. Interestingly, in Windows 7 and Excel 2010, if i login through admin user OR RUn excel.exe by clicking "Run as administrator" option (start-typoe excel-shift +right click-run as administrator)and then opening my excel file it does not give this error and more interestingly if after logging once through admin user and opening this excel file and closing it, I then log off and log in as normal user and open my excel file I still do not get this error.So the thing is that ONE TIME login as admin and opening this excel file temporariily fixes the issue.BUT the problem is that i need to distribute this application to various users and they will be logging it as normal users and i can not ask them to first login as admin user. Why this issue is ioccuring and what is the resolution for this? It’s very urgent , will really appreciate quick response.thanks.

I am trying to automate a totals sheet for data I have parsed from a larger file.
My current code creates a new workbook and adds data to a specified number of sheets, 1-12.
I am trying to add a sheet 13 named "Totals" that will give a totals from sheets 1-12.
I am unsure how to get a formula to be input into a cell from code without hard coding each cell.
In the following code, I am trying to get a sum for columns B to S of rows 11 to 22. so B9 =SUM(B11:B22)
I get a runtime error at this line in the code.

I am trying to use a loop to eliminate all the hard coding. Each of my columns B-S rows 11 to 22 will have similar loops for the formulas.
B11=SUM('1'!M:M)
B12=SUM('2'!M:M)
B13=SUM('3'!M:M)

C11=COUNTIF('1'!G:G,"H")
C12=COUNTIF('2'!G:G,"H")
C13=COUNTIF('3'!G:G,"H")

Thank you,

Simi

HI,

IN MY WORK , I HAVE TO ACKNOWLEDGE A LIK WHICH IS INSIDE THE MAIL.

FOR THAT I NEED A MACRO THAT IF THE MAIL COMES IN THAT FOLDER THEN IT WILL AUTO CLICK THE LINK AND AFTER OPENING THE LINK IT WILL ENTER ONE STRIP "DONE" IN THE GIVEN BOX AND PRESS AKNOWLEDGE BUTTON.

HOPE I'LL GET MY SOLUTION asap.

Attached Files

• Database.xls (201.5 KB)

I have a rather large vba application written for excel to generate assorted reports on SPC data. My histogram chart broke beginning with version 2007. Same code, different results. I am unfortunately not an excel guru so I'm hoping someone here might be able to tell me why this code that works in all versions of excel prior to version 2007 no longer works. Below is the code and screencaptures from versions 2003 and 2010.

Thanks,
Robb

[FUNCTION THAT CREATES THE CHART]
[SNIPPET OF THE COLLECTED DATA (there are 50 values in the Bell Curve)]


[RESULTS IN EXCEL PRIOR TO VER 2007]


[RESULTS IN EXCEL 2010]

Attached Thumbnails

     

Hi,


I have the following code in the cell code view mode , and in normal mode the value i see is .


Like i'm not sure what the formula/or function does to get that value.


thx