Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA Equation Solving Problem

Hi, I am an engineering student who basically doesn't have much knowledge in VBA. I need to build a VBA based system for piping. Practically, I wanted to put an equation in the userform that I'd made but I am now stuck with this type of equation (cubic). Does anyone have any idea how to solve this equation:

22400 * X = 231 * ((1-X) ^ 3) or 22400x=231(1-x)3

how do i solve the equation in VBA editor?


Post your answer or comment

comments powered by Disqus
I have written a program using VBA (I'm brand new to this) and have come accross a problem which I couldn't find an answer to anywhere.

My program has 5 x userforms, one of which is the "home" form where you can navigate to the others.
The program was running perfectly before and I don't know why this started happening - everything else seems to work beautifully.

When I open the spreadsheet, the home form comes up, but when I click on any one of the navigation options it throws an error at me. Run-time error '380': Could not set the Text property. Invalid property value.

Now the strange part is, when I "end" the program and then press "go" again, the code works fine.
My thought was, the workbook isn't activating properly - If I put the following in my "Userform_Activate" sub:


	VB:
	
msgbox(activeworkbook.name) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It throws another error at me (on first run only, after which it displays the message perfectly).

Does anyone know why this is happening, or how to solve it?

Thanks

When I want to open a file that contains Visual Basic program I face this
Error message:
Opening the VBA project in this file requires a component that is not
currently installed. This file will be opened without the VBA project. For
more information, search Microsoft Office Online for “VBA converters”.
Searching Microsoft Office Online I didn't find anything that solves my
problem

OK. I'm new to this group, but now I need help!

There is something fundamentally wrong with either the event handling in Excel VBA text boxes, or myunderstanding of it.
I have been experimenting with this for two days now, and not getting anywhere. Maybe someone in the group can provide me with some insight.
I have built a toy application to reduce the problem to its simplest terms:A UserForm1 object with four TextBoxes and a CommandButton;Tab order is TextBox1, TextBox2, SwapButton, Chooser, TextBox3;Initial TextBox values are 001, 002, 003;Each TextBox1..3 has an Entry handler that selects the first n characters, where n is the box number;Chooser is a TextBox whose BeforeUpdate handler sets the focus to TextBoxn, where n is Chooser.Value;Chooser also sets the value of the global object variable objActive to be the chosen TextBox;SwapButton uses objActive to toggle the focus between TextBox1 and TextBox2.SwapButton works entirely as expected: the selection switches between the first char in TextBox1 and the first two chars in TextBox2.The behaviour of Chooser is entirely arcane:when the value in Chooser is set to 3, it functions as expected: the selection switches to the first 3 chars of TextBox3;when the value in Chooser is set to 1, the behaviour becomes a little unexpected: TextBox1.SetFocus causes Chooser_BeforeUpdate to run again (re-entered), during which TextBox1_Enter runs before the inner Chooser_BeforeUpdate exits, the outer exits without triggering anything, and selection switches to the first char of TextBox1;when the value in Chooser is set to 2, the behaviour becomes very strange:Chooser_BeforeUpdate runs and does a SetFocus on TextBox2,Chooser_BeforeUpdate runs again (re-entered),the inner Chooser_BeforeUpdate exits,TextBox2_Enter runs,the outer Chooser_BeforeUpdate exits (there are now no event handlers running),TextBox1_Enter runs !!! and selects the first char of TextBox1,the selection is set to the entire contents of TextBox1 !!! as if it had been tabbed to;objActive continues to point to TextBox2.The only difference I can see is that TextBox1..2 both come before Chooser in the TabOrder, but this turns out not to be the whole story.

That TextBox3 would be the natural destination on exit from Chooser seems to account for the lack of re-entry of Chooser_BeforeUpdate (confirmed by adding another TextBox in the TabOrder between Chooser and TextBox3), but not for selection of TextBox1. The selection moves to the first 3 chars of TextBox3 as expected.

In reality, what's happening is that if SetFocus is run on any other control than the next control in the TabOrder, the focus is moved by 2 !!! tabstops from Chooser, after the control to which the focus was moved is Entered.

Setting Cancel=True in the outer Chooser_BeforeUpdate seems to solve the problem; setting Cancel=True in the inner iteration causes "Run-time error '80004005' Unspecified error", and exit the inner event handler sideways to the outer handler. Not very helpful!

So, after much BS&T (bitching, speculating, and typing), I seem to have the framework for a workaround in my main program, but I crave an explanation. Am I doing something wrong? Is the Event-handling broken? Did they mean to do that (why?).

Thanks for any insight,
FWB

Hi folks,

may be I am too stupid to find the right google search arguments for my
problem.
Here goes:

EXCEL 97 Workbook on Windows 2000 prof. machine with VBA code in Module
1. Works as intended.

Now I hide the VBA code via VBA project properties and save ok.
Subsequent attempts to save the workbook will store it under a new
(random ?) filename without ".xls". An associated message will tell me
so as well as that the
file cannot be renamed and I should close this file.

What am I missing ?

Thanks in advance
--
Friedrich Hofmann
FHofmannMZ@arcor.de

I ask here for help in the problem I am facing with my VBA-application.
Namely RICHTX32.OCX (in windowssystem32 -folder; versio nro: 6.00.8804; SP4)
is not functioning in my Excel-program. As the additional control, it refuses
to be added onto userform /-interface by informing that "The subject is not
trusted for the specified action". I have tried already the registration
statement: "regsvr32 windowssystem32RICHTX32.OCX", which gives the message
"DLLregisterserver in windowssystem32richtx32.ocx succeeded" as the sign
of succesful registration. But still it does not work. Can anyone there help
with this; what should I try next? - My operating system is XP with the
obviously latest updates installed.

then I was advised to download and install the current version ( 6.1.97.82)
of RICHTX32.OCX (from
http://activex.microsoft.com/controls/vb6/richtx32.cab). It did not eliminate
the problem; the same message pops up "The subject is not trusted for the
specified action" in trying to drag the control onto a Userform. Does anyone
know what would solve the problem?

Hi,

I'd like to fill some numbers of Excel 2000 cells with a formula,
so I wrote code in VBA (see below):

Cells(r,c).Formula = "=VLOOKUP($A4;[dane.xls]
where r - row, c - col, $A4 - cell with searching string

When I run VBA code I see error message:
Run-time error 1004:
Application-defined or object-defined error.

When I paste the formula into cell or type it everything is ok,
only VBA code makes problem.
What could be wrong, how should I solve the problem.
Thanks in advance.

Regards,
Jacek

Hi im new to excel and im trying to figure out this problem...

Essentially, I am supposed to use Excel to solve a problem given the following information.

-Between 1961 and 1970, 90 million cars total were produced.
-The life span of a car is assumed to be 10 years, therefore cars that were manufactured in 1961 are retired in 1970.
-Each year the number of cars produced is equal to 1.3 times the number of cars that were retired that year. Basically, the number of manufactured cars in 1970 equals 1.3 times the number of cars manufactured in 1961.

Given this data I am supposed to interpolate how many cars were produced each year between 1961 and 1970. Any suggestions how I can go about this? I am familiar with graphing in excel but equation solving aint my strong point. Any help would be great. Thanks.

Hi

Is it possible to write something in VBA that solves the following problem.

List of Numbers: 1,2,3,4,5,6
Result = 12

What I am looking to do is find the result 12 within the list of numbers and all the combinations. So here my answer could be:

1,5,6 = 12
4,5,3 = 12
4,2,6 = 12
......

Is this possible? I have done it using basic excel formula's but can only do it for 14 numbers due to the number of combinations and memory usage taken up in Excel.

Hope Some One can help.

Laiders

First of all, thanks for trying to understanding my question. I sincerely need your help!

Here comes the situation. I have got a customer information workbook (attached). Please kindly download and open it.

Firstly, I have an older version of customer information list (“client list”). It contains some information that I need to input into my new customer information list (“CustomerInfo Data”). For enhancing efficiency, I have made a userform for inputting information (the orange box). As the older version got only some of the columns I need in the new one. Hence, I did organize a “Search Customer No.” button for users. For example, once I key in ‘P-01’ for customer no. and click search, it fills in other fields which the older version of client list contains. And I will key in other fields manually.

The problem comes now. (Please kindly click into client list. Thanks.)

You may notice that there are two records for the same customer number. As P-02 Mr. B got 2 different accounts with different relation (field C) and business partner (field D), we have inputted these 2 accounts under the same customer number P-02.

However, my search button can only search the first record of P-02 (i.e. I can only find out row 3 in client list but cannot find out row 4 by repeatedly clicking the search button).

Moreover, the offset code seems disabled, in other words, even I can search the record of P-02, it only fills in other fields according to P-01.

So, what I need is that:
1) I need my search button to be able to search all records of P-02 by clicking one more time.

2) Upon successfully searched the correct record (row), I need the userform to automatically fill in other fields with the corresponding row in the “client list”.

Again, truly thank you for spending time on helping me out, even if you don’t know what I am talking about.

P.S. The attached workbook is almost the full version of the real one but was done with slight modification only by hiding my client information and modified lookup list. Please kindly just modify the searching button VBA code if possible. (Shown below)


	VB:
	
 
Private Sub cmdSCN_Click() 
    Dim myFindText As String 
    Dim rowFound As Integer 
    Dim foundCell As Range 
    Dim n As Long 
    With Worksheets("Client List").Range("A1:A4") 
        myFindText = txtCustomerNumber.Value 
         
         
        If myFindText = "" Then 
            MsgBox ("Enter customer number to find") 
            Exit Sub 
        End If 
         
        If .Find(myFindText, LookIn:=xlValues) Is Nothing Then 
            MsgBox ("Not Found") 
Else: 
            StartSearch = 2 
             
            Set foundCell = Worksheets("Client List").Cells(StartSearch, 1) 
            n = .Find(myFindText, foundCell, LookIn:=xlValues).Row 
             
            StartSearch = n 
             ' so that next time Find button is clicked, search starts from
             ' the cell found last time.  So can find all occurrences by
             ' clicking Find repeatedly.
             
            txtRowNumber.Text = n ' moves to the found row
             
            txtName.Value = foundCell.Offset(0, 1).Value 
            cboRelation.Value = foundCell.Offset(0, 5).Value 
            txtBusinessPartner.Value = foundCell.Offset(0, 6).Value 
            txtSystemTIN.Value = foundCell.Offset(0, 4).Value 
            txtIdentificationNumber.Value = foundCell.Offset(0, 7).Value 
             
        End If 
         
    End With 
     
End Sub 

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

Firstly, I am new here and would like to say hello. I apologize for such a long post, but I'm at my wit's end.

Recently, I reformatted my hard drive, then clean install Windows XP Home SP2, all drivers and MS Office XP. All updates were downloaded and installed from MS and Documents were restored from a backup. I synchronize with an IPAQ 3650 (Windows CE) & downloaded Activesync 4.1. I also have Norton IS Security 2005 and NSW 2005 Premier installed along with a number of other programs, basically replicating my system before the reformat, which was stable for a couple of years. The reason for all this was that the system operated erratically after loading Roxio Easy CD and I couldn't solve the problem, so decided to proceed as above, since I've done it a couple of times in the past with no problems.

I'm a low level user of Excel, only knowing how to do basic formulas and never record macros. I'm not even sure what VBA is. The problem that's occurring is threefold.

In some random spreadsheets, when macro security is set to medium, I get a warning, "filename.xls contains macros". It gives me a choice to disable or enable macros, and when I choose disable, get the message, "This workbook contains a type of macro (Microsoft Excel 4.0 or Visual Basic) that cannot be disabled. There may be viruses in these macros. If you are sure this workbook is from a trusted source, click Yes. Open the workbook?"
When I click yes, the workbook opens and I can avoid this entirely by going into Tools and selecting "low" for macro security, however, am concerned that I've never had to do any of this previously over 4 years of using Excel. My guess is that this is also tied in with the other issues.

The second problem is that functions such as "sum" and "multiply" do not work, even in a new workbook. When I first enter them, everything is fine, but after saving and closing it, they do not work when reopened. For example, the original sum is displayed, even when adding data to the range. On the tools, options, calculation tab, Calculation is set to automatic, & in fact formulas won't update even if manual is selected and Calc Now button is clicked. The only way to get formulas to work is to delete the formula, then re-enter it and select the range all over again, but then the next time the workbook is opened, I have to go thru the same process again.

The third problem I have is that when the workbook is synched to my IPAQ, the file name is there, but when I go to open it, there is no data, and it reads, "This sheet is a placeholder for an unsupported sheet type"

I have tried uninstalling Excel and reinstalling, uninstalling ActiveSync 4.1 and reinstalling 3.7 (which would no longer synchronize), then reinstalled 4.1. At this point, Excel is totally useless to me and I am completely at a loss as to how to proceed. My best guess is that there is some kind of problem with Activesynch, but I've not a clue as to how to resolve it. Any help or guidance will be extremely appreciated.
Thanks,
Rich B

Dear All,

The is my first visit to this forum and I am looking forward to learning from some great VBA minds out there. I have what I hope will be a simple to solve problem. I am using the following code to copy 5 rows from a user form to the bottom of the form. The code was running perfectly until I entered an array formula in column x whereupon the macro breaks at the line underlined. I get the error message "can't change part of an array". The strange thing is, a manual copy and paste works perfectly. Any suggestions anyone? Thanks in advance.


	VB:
	
 collate() 
    Dim r As Range 
    Set r = Cells(Rows.Count, "H").End(xlUp).Offset(2, -7) 
    Rows("6:8").Copy 
    r.PasteSpecial Paste:=xlPasteFormulas 
    r.PasteSpecial Paste:=xlPasteFormats 
    r.PasteSpecial Paste:=xlPasteValidation 
    r.Resize(3).EntireRow.SpecialCells (xlCellTypeConstants) 
    r.Offset(0, 11).ClearContents 
    Dim s As Range 
    Set s = Cells(Rows.Count, "K").End(xlUp).Offset(1, -10) 
    Rows("9:10").Copy 
    s.PasteSpecial Paste:=xlPasteFormulas 
    [U]s.PasteSpecial Paste:=xlPasteFormats[/U] 
    s.PasteSpecial Paste:=xlPasteValidation 
    s.Resize(2).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents 
    Application.CutCopyMode = False 
End Sub 

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


Hi,
I have a workbook with data on sheet1 and 3 charts on sheet2.
3 Charts because there is too much data for 1 chart.
The Y-scale values of the charts are automatically set.
This works by entering the following code in sheet1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim intIndex As Integer
Dim r As Range

For intIndex = 1 To 3
Set r = Range("metingen" & CStr(intIndex))
If Not Intersect(r, Target) Is Nothing Then
With Worksheets("Grafiek").ChartObjects("Chart" & CStr(intIndex)).Chart.Axes(xlValue)
.MinimumScale = Int(Application.Min(r)) - 0.5 'Int returns integer portion of number.
.MaximumScale = Int(Application.Max(r)) + 0.5
End With
Exit For
End If
Next

End Sub

Here is the problem:
A user enters data by using a userform.
The chart will be adjusted when excel detect any changes on the worksheet.
But because of entering data by a userform it doesn’t detect any changes. I have to select the changed cells my self before the chart will adjust.
How can I solve the problem so that excel detects changes after pressing OK on the userform?

Thanx,
Fluppe

Hi all......... I'm totally new to VBA for excel.... Is there a way to set a macro such that you dont actually watch it run in real time, rather, the results simply display once the operation is complete.....

Apologys for my complete ignorance....

mgri

Hi,

I have an Excel 2007 workbook with several sheets and all operations are handled by macros called from command button controls. In the Workbook_Open event, the following code sorts, establishes AutoFilter, and protects the Detail worksheet (variables definitions and other code excluded):

	VB:
	
 
Detail.Unprotect Password:="zMacr0sRul3" 
If Detail.FilterMode Then Detail.ShowAllData 
Call Module1.SortData 
Detail.EnableAutoFilter = True 
If Not Detail.AutoFilterMode Then Detail.Range("DetailHeaders").AutoFilter 
Detail.Protect Password:="zMacr0sRul3", UserInterFaceOnly:=True, AllowSorting:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I import data by month into Detail from an external source using sql. The code uses Advanced Filter to determine if any of the months already exist in the current data and, if so, deletes that data prior to the new import using the following:

	VB:
	
 
 'Ensure all Detail data is displayed.
If Detail.FilterMode Then Detail.ShowAllData 
 
 'Filter the Detail data to display rows that would be replaced.
Detail.Range(Detail.Cells(lHdrRow, lDateCol), _ 
Detail.Cells(Detail.Rows.Count, lDateCol).End(xlUp)) _ 
.AdvancedFilter Action:=xlFilterInPlace, _ 
CriteriaRange:=RunReport.Range("RunDateCrit"), Unique:=False 
lRows = Detail.Range(Detail.Cells(lHdrRow, lDateCol), Detail.Cells(lLastRow, _ 
lDateCol)).Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 
If lRows > 0 Then 
     'Delete the displayed rows.
    Detail.Range(Detail.Cells(lHdrRow + 1, lDateCol), _ 
    Detail.Cells(Detail.Rows.Count, lDateCol).End(xlUp)) _ 
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code worked when originally written but now produces a "Delete method of Range class failed" message. Debugging shows that Advanced Filter works correctly and all variables have the correct values when the delete statement executes. Removing the AutoFilter has no effect but unprotecting the worksheet eliminates the error. There are no Protect or Unprotect statements outside the Workbook_Open event and the UserInterFaceOnly:=True argument should allow the deletes. Since it did work originally, some of the macro code changes I made later must have caused this problem. The only changes that seem pertinent were related to sorting. I found that the AutoFilter sort options available to the users when reviewing the Detail data failed because the sheet was protected so I unlocked the cells in the data range and added AllowSorting:=True to the protect statements in the Workbook_Open event. I tried reversing those changes but it had no efffect. Any ideas on what could cause this error? The AutoFilter is an absolute requirement from the users and the non-data portions of the Detail sheet must be protected (so please don't suggest I just get rid of them). I can unprotect the sheet before the deletes and reprotect it after but that has risks. I will be using macros and VBA a lot in the future so I'm more interested in understanding the problem.

Thanks,
Michael Bailey

Hi All,

I have a problem with a routine that works on one computer running Office 2000, but not on another.

I have an add-in that runs from Excel and reformats a spreadsheet. Once this is completed, then this data is appended to a table in an Access database. Everything runs fine on our laptop, but there is a problem getting it to work on the client's machine. The problem occurs when it tries to run the sub routine that connects to the database.

I am acting as a middleman on this issue, as the problem is occuring overseas and is trying to be solved by someone else. This is their description of the problem:

"No luck with the macro. It still fails, despite upgrading the installation to SP2 of Office 2000, which is the same as mine.

It crashes when trying to connect to the specified database, wherever that is. The error message received is:

Runtime error '-2147220999 (800401f9)'
Method 'Open' of object '_Connection' failed

The number in brackets is hex code for an error that says "Error in the DLL" according to Knowledgebase.

We found one DLL that office setup was unable to repair, that was an earlier version than mine - VGX.DLL
I replaced that but the problem continues."

Does anyone have any experience with a similar sort of problem and solutions on how to solve it?

All help much appreciated.

Cheers,

Hi,
i need procedure in VBA to import data into access from csv excel file without some records,, as header and footer. Example,,, i have table in csv file, which contains some
sentence which not belong table date
_______________________________________
A1 this is some sentence title.......
A2 title
A3.......
A7 DATA DATA DATA DATA DATA
A8 DATA DATA DATA DATA DATA
......
....
A256 DATA DATA DATA DATA
A257 this is some sentence
________________________________________________

My Acess shoud contain only record between A7 to A256. Does anyone knows procedure or whatever in VBA who solves my problems ?

thanks a lot

Hi all,

First post but been following the forum for awhile. I am trying to incorporate solver into VBA code. The problem I am having is that it is not recognizing my SolverOk command (and possibly my SolverSolve command) unless I first run solver manually inside of Excel. Then my code works perfectly. If I don't do this the SolverOk portion of the code just doesn't work. It doesn't return any errors or anything it just goes through the code w/o doing anything. I have solver checked in Tools->References and I have it installed as an add-in.

Any ideas of what could be going on? Here is my code:

Code:
 
Sub Run_Solver()
i = 0
Do While i

I need some help with some vba code i am trying to write. I a value (A) the user will select different increase or decrease % amounts that need to be added or subtracted from A. So the first % amount will be applied to A then a new amount is calculated. Then % amount 2 will be applied to the newly calculated value. This trend continues.

A
%*A=B
%*B=C
%*C=D

You get the picture

My problem is that the discount if 10% if value A is $100, the ending value needs to be $110 not just the $10.

Can someone please help me with this?

Thanks!

I have problem with formating cells. When I delete cells value in column A VBA doesn't remove borders. Help pls
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Long
    Application.ScreenUpdating = False
    For x = Range("A65536").End(xlUp).Row To 2 Step -1
    If Cells(x, 1) = "" Then    *****problem is this condition??? **** 
 With Range(Cells(x, 1), Cells(x, 36)).Font
        .ColorIndex = 0
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range(Cells(x, 1), Cells(x, 36)).Borders(xlLeft).LineStyle = xlNone
    Range(Cells(x, 1), Cells(x, 36)).Borders(xlRight).LineStyle = xlNone
    Range(Cells(x, 1), Cells(x, 36)).Borders(xlTop).LineStyle = xlNone
    Range(Cells(x, 1), Cells(x, 36)).Borders(xlBottom).LineStyle = xlNone
    ElseIf Cells(x, 1)  "" Then
    
    With Range(Cells(x, 1), Cells(x, 36)).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Range(Cells(x, 1), Cells(x, 36)).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Range(Cells(x, 1), Cells(x, 36)).Borders(xlRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Range(Cells(x, 1), Cells(x, 36)).Borders(xlTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Range(Cells(x, 1), Cells(x, 36)).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    End If
    Next x
    Application.ScreenUpdating = True
end sub


Hi,

I have a problem with the following piece of code:

Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEFT($B3,3)=PRJ"
For this I require the PRJ to be in quotation marks as such "PRJ" but VBA wont allow me to have the code like this.

Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEFT($B3,3)="PRJ""
Can anybody help?

Hi,

I am starting to work with an Excel file I have not worked with before.

There is a table TABLE1 in the Excel file, which is connected to a MS Access table via MS Query.

The TABLE1 should be refreshed every day via VBA macro like this:

Selection.QueryTable.Refresh BackgroundQuery:=False

This command doesnt function, because, effectively, even if I try to refresh TABLE1 manually (right mouse, refresh data), I get this message:

"[Microsoft][ODBC Driver Manager] Data Source name not found and no default driver specified"

After this message I have a window: "Select Data Source".
Here I select: MS Access Database (Type: User).
In the next window "Login" I just have to press OK, because if I press the "Database" button, the .MBD database is already selected.
After this, the TABLE1 is refreshed.
THE REFRESHING OF TABLE1 IS FUNCTIONING AFTER THIS - MANUALLY OR VIA MACRO COMMAND.

MY PROBLEM IS:
I wanted to set the connection to a MS Access database in the macro in MS Excel, and not manually. But I found out, that the connection is already set - if i select the TABLE1, and write this command in the immediate window:

MsgBox Selection.QueryTable.Connection

I get a full description of the connection:

"ODBC;DSN=Access;DBQ=M:LiquiditätCash_Flows_AG.MDB; DefaultDir = M:Liquidität; DriverID = 25; FIL = MS ACCESS; MaxBufferSize = 2048; PageTimeout = 5;

Despite of this, I wrote this in the macro:

Selection.QueryTable.Connection = "ODBC;DSN=Access;DBQ=M:LiquiditätCash_Flows_AG.MDB; DefaultDir = M:Liquidität; DriverID = 25; FIL = MS ACCESS; MaxBufferSize = 2048; PageTimeout = 5;"
Selection.QueryTable.Refresh BackgroundQuery:=False

If I run the macro now, the TABLE1 doesnt get refreshed and I get the message:

"[Microsoft][ODBC Driver Manager] Data Source name not found and no default driver specified"

and the two windows ("Select Data Source" and "Login") again.

How should I get the setting of the connection work via macro in MS Excel?
Or is the problem caused by some system settings?

Thanks
Katarina

Hi,

Anyone know the maximum length ( in characters ) of an equation which VBA can write into a cell ? I'm trying to write this equaltion :

"=SUMIF(DRGNumber,RC[-10],GBoxEnv)=0 """" COUNTIF(DRGNumber,RC[-10])=SUMIF(DRGNumber,RC[-10],GBoxEnv)""Internal"" (COUNTIF(DRGNumber,RC[-10])*2)=SUMIF(DRGNumber,RC[-10],GBoxEnv)C""External"" (COUNTIF(DRGNumber,RC[-10])*2) DRGNumber "

which is 233 chars but keep getting an error. I'm pretty certain that the syntax is correct, but if anyone spots an error then please let me know.

TIA,

Iain

******** ******************** ************************************************************************>Microsoft Excel - TrackingDDay.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD2E2F2G2D3E3F3G3D4E4F4G4D5E5F5G5D6E6F6G6D7E7F7G7=
ABCDEFG1DateProductReferenceSalesDataNumberRejectReason21/08/2007GFX2697192Tracked434431841434431841Verbal*Reject36/08/2007GFX2704040Tracked422639359#N/A#N/A417/08/2007GFX2726101Tracked422639359#N/A#N/A520/08/2007GFX2728774#N/A#N/A#N/A#N/A620/08/2007GFX2730044#N/A#N/A#N/A#N/A721/08/2007GFX2730866#N/A#N/A#N/A#N/ATracking*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Well I missed my first break, lunch and evening break trying to come up with an easier way to this. I come here humbly to ask the Guru's for advice.

Here is a snippet of a sheet in a horrid workbook that I am trying to simplify.

Product is the Product Sold
Reference is generated by system
SalesData is where Reference appears if the order has shipped and is Tracked.
Number is generated when Reference appears in the SalesData it will also appear on the Reject if there was a problem.
The reason for the problem is in G

All I need from all this is;

Has it Tracked?
If No, has it been more than 7 days since you placed it?
If Yes, then investigate the order
If No, don't worry about it until 7 days has passed
If Yes, has it appeared on the Rejects?
If No, then it's Tracked.
If Yes, what was the reason.

There are about 1100 products, which is why I thought there might be a VBA to solve this rather than the current process of a monster Workbook.

Sincerely,
WageSlave101

Dear advance excel user:

I had a small vb macro, try to solve 2 qadratic eq with 2 variable

----------------------------------------------------------------

Sub Macro1()

'To run a macro in Excel, you must set the Solver reference in
VBA/Tools/References

'sets variables cells, there is not objective in your system of
nonlinear equations
ret = SolverOk(MaxMinVal:=1, ValueOf:="0", ByChange:="$E$7:$F$7")
'adds constraints.
ret = SolverAdd(CellRef:="$E$9:$E$10", Relation:=2,
FormulaText:="$F$9:$F$10")
'solves problem
SolverSolve

End Sub

------------------------------------------------------------------
when I run it , a window popup say internal error, exhaust memroy

what may go wrong?
please help
eric


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