Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Excel Web Query With Login And Password

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi
I have a website with "site" name, "login" and "password". I am trying to access this web site with all of the above information supplied from excel using the following code.
Code:

Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = "http://fioumes.emea.nsn-net.net:7001/login"
Const PostUser As String = "SITE=prepro" 'Change user name here
Const PosPassword As String = "&LOGON_ID=lleo" 'Change password here
    Const PostPassword As String = "&PASSWORD=anna123" 'Change password here
MyPost = PostUser & PosPassword & PostPassword
    
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(1, 1))
    .PostText = MyPost
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
    
End Sub


It is not working.

This is the data from "View Source" of this website

Code:

 
 function setInitialFocus()
            {
                var focusField = PageHelper.getHiddenFieldById( "SITE" );
                if( focusField.value != "" )
                {
                    var loginField = PageHelper.getHiddenFieldById( "LOGON_ID" );
                    if( loginField.value != "" )
                    {
                        var passwordField = PageHelper.getHiddenFieldById( "PASSWORD" );
                        if( passwordField.value == "" )
                            focusField = passwordField;
                    }
                    else
                        focusField = loginField;
                }
                focusField.focus();
            }
 
            function setTimezoneOffset()
            {
                var date = new Date();
                var timezoneOffset = date.getTimezoneOffset() * -1;
                PageHelper.addHiddenFormField( "MAIN", "TIMEZONE_OFFSET", timezoneOffset );
            }
            
            function forgeCredentials() {
                document.getElementById("forgedUsername").value = 
                    document.getElementById("SITE").value 
                    + "/" + document.getElementById("LOGON_ID").value;
                // alert(document.getElementById("forgedUsername").value);
                
                document.getElementById("forgedPassword").value =
                    document.getElementById("PASSWORD").value;
                // alert(document.getElementById("forgedPassword").value);
            }
 
            function checkPODFrame() {
                //This method is used to prevent from showing login screen in subframe of the POD,
                //it should fill the whole POD window.
                if (parent.PodMain != undefined) {
                    parent.location.reload();
                }
            }
            
            function doAppContainerLogon() {
            	if ("/login"=="/j_security_check"
						|| "/login"=="/") {
            		// Manual validation of user passed, so container login module should succeed
            		// So, submit credentials.
					document.forms[0].submit();
            	}
            }
 
        </script>
    
</head>


Appreciate yur help.


Similar Excel Video Tutorials

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Protect and Unprotect All Worksheets at Once
- This will allow you to protect or unprotct all worksheets in a workbook at once. It doesn't allow you to input a passwor
Open a Word Document from Excel
- This Microsoft Excel macro will open a Microsoft Word document from excel. With this macro, you can open any word docume
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note

Similar Topics







I have this huge formula that I am using to create a field with text in it defining ingredients in food items. Is there anyway I can break it up?

Here's the formula:
Quote:

=C2&IF(SUM(D2:E2)>0," -","")
&IF(D2>0,"Vegetarian","")
&IF(D2=3," on request","")
&IF(SUM(D2:E2)=2," or ","")
&IF(E2>0,"Vegan","")
&IF(E2=3," on request","")
&IF(SUM(D2:E2)=2," on request","")
&IF(SUM(F2:O2)>0," contains (","")
&IF(P2=1," May Contain","")
&IF(F2>0,"Alcohol","")
&IF(AND(G2=1,H2=0,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(Q2=1," May Contain","")
&IF(G2>0," Dairy","")
&IF(AND(H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(R2=1," May Contain","")
&IF(H2>0," Eggs","")
&IF(AND(I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(S2=1," May Contain","")
&IF(I2>0," Gluten","")
&IF(AND(J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(T2=1," May Contain","")
&IF(J2>0," Soy","")
&IF(AND(K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(U2=1," May Contain","")
&IF(K2>0," Peanuts","")
&IF(AND(L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(V2=1," May Contain","")
&IF(L2>0," Nuts","")
&IF(AND(M2=1,N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(W2=1," May Contain","")
&IF(M2>0," Seeds","")
&IF(AND(N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(X2=1," May Contain","")
&IF(N2>0," Fish","")
&IF(AND(O2=1,SUM(F2:O2)>1)," &","")
&IF(Y2=1," May Contain","")
&IF(O2>0," Shell Fish","")
&IF(SUM(F2:O2)>0,")","")




Hi all,

I have some troubles that i hope you can help me with.

Description:
I'm trying to create VBA code so i can get file data from URL address. But, when i run the code a login window/box appears. When i enter login details then i get the data to excel.

If i run this code once more time then it works. But, that because i am already logged in.

What i need help with:
I would like to know how to login and query file data without getting login window/box.

This is how the code looks at the moment (Remove login details)

Code:

Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = "http://fds.nasdaqomxnordic.com/basicdata/yesterday/OMXCCAPGI4010_Index_EveningReport.txt"
Const PostUser As String = "login=User Name"
Const PostPassword As String = "&pass=Password"
   
MyPost = PostUser & PostPassword
   
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(1, 1))
    .PostText = MyPost
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
   
End Sub


Appreciate all help that i can get on this one, many thanks.


Hello,
I know there is possibility to login into gmail using macro, but I am not able to log there
I have this macro : Code:

Sub gmail()

   
Const MyUrl As String = "http://gmail.com"
Const PostUser As String = "Email=My_mail"
Const PostPassword As String = "&Password=pass"
   

MyPost = PostUser & PostPassword
   
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(1, 1))
    .PostText = MyPost
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
   
End Sub


but it only save login page into excel, I know there is possibility tu use IE but I need to use only excel, I will appreciate any help, thanks !


Hi all,

I have the following IF function

Code:

IF(R3="x", Q3/12, 0)+IF(S3="x", Q3/12, 0)+IF(T3="x", Q3/12, 0)+IF(U3="x", Q3/12, 0)+IF(V3="x", Q3/12, 0)+IF(W3="x", Q3/12, 0)+IF(X3="x", Q3/12, 0)+IF(Y3="x", Q3/12, 0)+IF(Z3="x", Q3/12, 0)+IF(AA3="x", Q3/12, 0)+IF(AB3="x", Q3/12, 0)+IF(AC3="x", Q3/12, 0)


which is a combination of a whole lot of smaller IF functions.

It works well but is very long.

Does anyone know a way I can condense it?

Many thanks,


Nicko


I'm trying enter the zip to into the form, then submit the form and pull the data from the query. For whatever reason the query isn't copying the data from the results and is copying from the store locator page (MyUrl). Any Ideas? Do I need to add a function for the submit button?

Code:

Sub Login_WebQuery_SessionID()
Dim MyPost As String

Const MyUrl As String = "http://www.walgreens.com/marketing/storelocator/find.jsp?tab=store%20locator" 'change URL here
Const PostUser As String = "combCriteria=63005" 'Change form name and user name here


'Attach the Login and Password
MyPost = PostUser

'Run Web Query
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Range("Sheet1!$A$1"))
    .PostText = MyPost
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
    
End Sub





Help!

I have a stupid long forumla that needs to be longer! There must be an easier way of doing and I'm hoping one of you guys can sort it.

=IF((AND(AF4="w",AG4="w")),"0")*(IF((AND(AF4="w",AG4="1c")),"1"),(IF((AND(AF4="w",AG4="1b")),"2",(IF ((AND(AF4="w",AG4="1a")),"3",(IF((AND(AF4="w",AG4="2c")),"w",(IF((AND(AF4="w",AG4="2c")),"4",(IF((AN D(AF4="1c",AG4="w")),"-1",(IF((AND(AF4="1c",AG4="1c")),"0",(IF((AND(AF4="1c",AG4="1a")),"1"),(IF((AND(AF4="1c",AG4="1a"))," 2"),(IF((AND(AF4="1c",AG4="2c")),"3"),(IF((AND(AF4="1c",AG4="2b")),"4"),(IF((AND(AF4="1c",AG4="2a")) ,"5"),(IF((AND(AF4="1b",AG4="w")),"-2"),(IF((AND(AF4="1b",AG4="1c")),"-1"),(IF((AND(AF4="1b",AG4="1b")),"0"),(IF((AND(AF4="1b",AG4="1a")),"1"),(IF((AND(AF4="1b",AG4="2c")) ,"2"),(IF((AND(AF4="1b",AG4="2b")),"3"),(IF((AND(AF4="1b",AG4="2a")),"4"),(IF((AND(AF4="1b",AG4="3c" )),"5"),(IF((AND(AF4="1a",AG4="w")),"-3"),(IF((AND(AF4="1a",AG4="1c")),"-1"),(IF((AND(AF4="1b",AG4="2b")),"3"),(IF((AND(AF4="1b",AG4="2a")),"4"),(IF((AND(AF4="1b",AG4="3c")) ,"5"),(IF((AND(AF4="1a",AG4="w")),"-3"),(IF((AND(AF4="1a",AG4="1c")),"-1"))))))))))))))))))))))))))))))))))

Any ideas?


what i have here is a code to pull my bank account balance. it all works right. however i have one problem. with my bank, once i log in the browser loads a processing screen, then 3-6 seconds later it will redirect to my actual account page. when i run the script i get a copy past of the processing screen. not at all what i want. i tried a time delay of all the way up to 30 seconds, but it is still the processing page i dont want. what code should i use to get the info i want.

thank you in advance,
james


Code:

Sub Login_WebQuery()

Dim MyPost As String
Const MyUrl As String = "https://online.bbandt.com/auth/pwd.tb"
Const PostUser As String = "UserName = "*******"
Const PostPassword As String = "&Password = "*******"

MyPost = PostUser & PostPassword

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(1, 1))
    .PostText = MyPost
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
End Sub





ok, I have the following formula Which I would like to add a third option, essentially "AR" could also equal "Z" However, If "AR" does equal "Z" then the answer must also be "Z"

Code:

=IF(AND(BG3="",OR(AR3={"Y","A"})),"Y","")


I thought the following would work but it's not quite right

Code:

=IFS(AND(BG4="",OR(AR4={"Y","A","Z"})),"Y","Z","")


I have uploaded an example sheet, the above code is located in column "S"

Any help would be much appreciated


Hi Excel gurus, I have a formula that says too long, heres my formula...
=LOOKUP(B141,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32 ,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65, 66,67,68,70,71,72,73,74,75,76,77,78,79,80,81,494,495,496,497,498,499},{"$11.00","$11.00","$9.50","$8 .00","$8.50","$15.00","$12.00","$21.50","$14.50","$21.50","$22.50","$18.00","$21.50","$26.50","$14.0 0","$14.50","$14.50","$19.50","$14.50","$14.50","$19.50","$9.50","$14.50","$28.50","$42.00","$40.00" ,"$14.50","$24.00","$18.00","$24.00","$9.00","$7.50","$42.00","$30.00","$33.50","$45.00","$25.00","$ 23.50","$21.00","$14.50","$7.00","$21.50","$23.50","$33.50","$9.00","$8.50","$21.50","$36.00","$36.0 0","$36.00","$8.00","$33.50","$30.00","$50.00","$58.00","$72.00","$12.00","$6.00","$21.00","$7.00"," $72.00","$30.00","$7.00","$15.00","$10.00","$35.00","$36.00","$40.00","$7.00","$3.00","$8.00","$25.0 0","$3.00","$4.50","$1.50","$6.00","$12.00","18","20","16","$25.00","$28.00","$25.00","$35.00","$25. 00","$18.00"})

Enter PLU here qty entered
81 16
79 18
39 21

the problem is I will have a lot more PLU numbers as time goes. No wI am stuck, please help thanx in advance...


3 sleepless nights.. I'm loosing my mind on this.

Here goes;

Cell D22 contains the following Formula;
Code:

=IF(OR(D17="Vr",D18="Pr",D19="SDr",D20="Wr"),TRIM(IF(D17="Vr","r","")&IF(D18="Pr"," "&"r","")&IF(D19="SDr"," "&"r","")&IF(D20="Wr"," "&"r","")),IF(OR(D17="Vy",D18="Py",D19="SDy",D20="Wy"),TRIM(IF(D17="Vy","y","")&IF(D18="Py"," "&"y","")&IF(D19="SDy"," "&"y","")&IF(D20="Wy"," "&"y","")),""))


As you can see in the formula, D17:D20 is checked for the existance of Xy or Xr and populates D22 as many Xy's or Xr's as it finds in the range.

This results in D22 populated with Nothing, between 1 and 4 y's or between 1 and 4 r's, D22 is then CF'd to shade red if any r's are populated, Yellow if any Y's are populated, or green if nothing is populated.

Now comes Sheet2,cell A1;

Cell A1 looks at D22 and needs to shade itself based on what the shading of D22 is.

So far, I have resisted VB and Macro's to try and learn/understand what the process is all about and build formulae's.

My final challenge is to Shade Sheet2,cell A1 by using the shading of D22 ONLY.
Can this be done? or am I chasing my tail..?

Cheers


Code:

    Range("AT2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1=""JAN"",1,IF(RC1=""FEB"",2,IF(RC1=""MAR"",3,IF(RC1=""APR"",4,IF(RC1=""MAY"",5,IF(RC1=""JUN"",6,0))))))"
    Range("AU2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1=""JUL"",7,IF(RC1=""AUG"",8,IF(RC1=""SEP"",9,IF(RC1=""OCT"",10,IF(RC1=""NOV"",11,IF(RC1=""DEC"",12,0))))))"


this code above looks at cell a1 to see what month it is then gives it the corresponding number ie jan = 1 , feb = 2 and so on, the code has been wrote in 2 cells, at2 calculates jan to jun and au2 calculates jul to dec

how can i put these in on cell, so that at2 looks at a1 and decides if it is 1 2 3 4 5 6 7 8 9 10 11 or 12


Code:

 
Sub Log()
     
    Dim ie As Object
     
    Set ie = CreateObject("InternetExplorer.Application")
     
    ie.navigate "http://www.excelforum.com"
     
    ie.Visible = True
     
    Do While ie.Busy And Not ie.readyState = 4
        DoEvents
    Loop
     
    DoEvents
     
    ie.document.all.Item("navbar_username").Value = "MyUser"
    ie.document.all.Item("Password").Value = "MyPass"
    ie.document.all.Item("submit").Click
     
End Sub


This is my attempt to create a login to this site using VBA. It does not work. If i can get it to work here, i would like to apply it to other business sites that i log into on a regular basis to save a great deal of time.

If someone can point out where I am going wrong, it would be helpful. Also, for the User and Password, i would like to be able to use a cell reference (A1 & A2)

Thanks


Code:

A1 "5"
B1 ">" or "<"
C1 "2"
D1 "=IF(A1&B1&C1,"True","False")"


...is not working, returning #VALUE! in D1. I notice that the evaluation appears to be (correctly) converting it to a string:

Code:

IF("5>2","True","False")


...is there any way I can have it evaluate properly? I am aware that I can do something like that shown below, but for obvious reasons am reluctant to do so!

Code:

=IF(B1=">",IF(A1>C1,"True","False"),IF(B1="<",IF(A1<C1,"True","False")))





if in cell a1 it says "MAR" how can i get cell b1 to say "3"

Code:

=IF($A3="JAN",1,IF($A3="FEB",2,IF($A3="MAR",3,IF($A3="APR",4,IF($A3="MAY",5,IF($A3="JUN",6,0))))))


i currently have this code but it only allows up to june if i add any more to this code it doesn't work

can anyone help.

The goal is if a1 says Dec then b1 to say 12 and so on...


I'm looking to create a mailto link. The issue i'm having is with the 255char limit i believe. I'd like to enter quite a few email addresses so that they'll automatically be entered into outlook when I click on the link. Is there a way to do this? all the addresses come from a different row of a column. I've concatenated them all with a comma delimiter so that formula can be used to enter after the mailto.

What I have:

=hyperlink("mailto:" & =$K$12&IF($K$13<>"",","&$K$13,"")&IF($K$14<>"",","&$K$14,"")&IF($K$15<>"",","&$K$15,"")&IF($K$16<>"" ,","&$K$16,"")&IF($K$17<>"",","&$K$17,"")&IF($K$18<>"",","&$K$18,"")&IF($K$19<>"",","&$K$19,"")&IF($ K$20<>"",","&$K$20,"")&IF($K$21<>"",","&$K$21,"")&IF($K$22<>"",","&$K$22,"")&IF($K$23<>"",","&$K$23, "")&IF($K$35<>"",","&$K$35,"")&IF($K$36<>"",","&$K$36,"")&IF($K$37<>"",","&$K$37,"")&IF($K$38<>"",", "&$K$38,"")&IF($K$39<>"",","&$K$39,"")&IF($K$40<>"",","&$K$40,"")&IF($K$41<>"",","&$K$41,"")&IF($K$4 2<>"",","&$K$42,"")&IF($K$43<>"",","&$K$43,"")&IF($K$44<>"",","&$K$44,"")&IF($K$45<>"",","&$K$45,"") &IF($K$46<>"",","&$K$46,"")&IF($K$47<>"",","&$K$47,"")&IF($K$48<>"",","&$K$48,"")&IF($K$49<>"",","&$ K$49,"")&IF($K$50<>"",","&$K$50,"")&IF($K$51<>"",","&$K$51,"")&IF($K$52<>"",","&$K$52,"")&IF($K$53<> "",","&$K$53,"")&IF($K$54<>"",","&$K$54,"")&IF($K$55<>"",","&$K$55,"")&IF($K$56<>"",","&$K$56,"")&IF ($K$57<>"",","&$K$57,"")&IF($K$58<>"",","&$K$58,"")&IF($K$59<>"",","&$K$59,"")&IF($K$60<>"",","&$K$6 0,""))

I've also tried to do things like display all the emails so that they can be copied to the clipboard after the excel file has been saved and then viewed as a webpage. This runs into the 1024char limit when leaving the concatenated addresses in a cell or even a text box. any fancy idea on a way to accomplish any of this?

Tom


Hi,

I am in need of a formula that will acheive the following

IF A1 = "Entry" then B1 = "Entry"
OR
IF A1 = "Exit" then B2 = "Exit"
ELSE
IF A1 = " " then B2 should be blank.

I'm a newbie. Any help would be appreciated.

I tried the following in B1
Code:

=IF(A1="Entry","Entry",IF(A1="Exit","Exit",IF(A1=" "," " )))


However this returns a "False" if cell value is blank

Thanks in advance


Is it possible to use Worksheet_SelectionChange to watch two or more cells similar to the way you can link check boxes together so that if one cell has a value, the other (cells) do not?


Here is an idea of what I am trying to do but of course the code does not work as is:


Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target = Range("L15") Then
Range("L15").Value = "r"
Range("L16").Value = ""
Range("L17").Value = ""
     End If

If Target = Range("L16") Then
Range("L15").Value = ""
Range("L16").Value = "r"
Range("L17").Value = ""
     End If

If Target = Range("L17") Then
Range("L15").Value = ""
Range("L16").Value = ""
Range("L17").Value = "r"
     End If

End Sub





I've got a conditional formula where I want to copy a value if it's already a number but if it isn't, I want it to be calculated from what's in the cell. So, if the cell has "90+2" in it, I want the new cell to say "92". I've tried this:

Code:

=IF(ISNUMBER(A11),A11,"="&A11)



but it just results in "=90+2". Hopefully there is some function that can do this easily...


Thanks!


Heres my Problem


IF B1 has a possible value ranging from 1 - 5,
and IF the value in E1 is equal or great than 2,5,10,10,15
BUT 2,5,10,10,15 need to match to specific ranges set in B1
1=2, 2=5, ,3=10 ,4=10 ,5=15
THEN IF TRUE "WITHIN" IF FALSE "NEEDS UPDATE"

Got help earlier with this formula:
=IF(AND(B1=1,E1<=5),"WITHIN","NEEDS UPDATE") :D Worked Great!!!!

This was my attempt at expand that formula:
Code:

=IF(AND(B1=1,E1<=2),"WITHIN","NEEDS UPDATE",IF(AND(B1=2,E1<=5),"WITHIN","NEEDS UPDATE",IF(AND(B1=3,E1<=10),"WITHIN","NEEDS UPDATE",IF(AND(B1=4,E1<=10),"WITHIN","NEEDS UPDATE",IF(AND(B1=1,E1<=5),"WITHIN","NEEDS UPDATE")))))


Error too many Arguments

Next I tried this one:-----and many more
Code:

=(IF(AND(B1,{1,2,3,4,5},E1<=,{2,10,10,15},"WITHIN","NEEDS UPDATE")))


Contained Errors

Can anyone help ----Thanks in Advance


I've researched a few options but havent been able to make anything work.
Here is the formula I know would would if the limit were not 7....

=IF(C5="AT",K5*9.5%,IF(C5="Cr",K5*9.8%,IF(C5 ="Du",K5*9.8%,IF(C5="OH",K5*9.5%,IF(C5="PWC",K5 *9.5%,IF(C5="Sc",K5*9.8%,IF(C5="Sp",K5*9.8 %,IF(C5="To",K5*9.8%,IF(C5="Tr",K5*9.8%, IF(C5="UTV",K5*9.8%))))))))))

Well I've added a few more values now totaling 14...

I tried this and I get a "false" AND then the correct answer, obviously because the correct value is in the second IF statement.

=IF(C5="ATV S",J5*9.5%,IF(C5="ATV U",J5*9.5%,IF(C5="Cr",J5*9.8%,IF(C5="Cr T",J5*9.8%,IF(C5="DS",J5*9.8%,IF(C5="MX",J5*9.5%,IF(C5="Off", J5*9.5%)))))))&IF(C5="PWC",J5*9.5%,IF(C5="Scr" ,J5*9.8%,IF(C5="Sta",J5*9.8%,IF(C5="Sp",J5 *9.8%,IF(C5="Sp T",J5*9.8%,IF(C5="Tr",J5*9.5%,IF(C5="UTV",J5*9.5%)))))))

The I tried this, but I get "formula contains an error"

=LOOKUP(C5,{"ATV S","ATV U","Cruiser","Cr T","DS","MX","Off","PWC","Scr","St"," Sp","Sp T","Tr","UTV"},{J5*9.5%,J5*9.5%,J5*9.8% ,J5*9.8%,J5*9.8%,J5*9.5%,J5*9.5%,J5*9.5%,J5*9.8%,J 5*9.8%,J5*9.8%,J5*9.8%,J5*9.5%,J5*9.5%})

I'd REALLY appreciate any help you can give...I'm stumped.

-T


I have the following code for a dde input everyhting is working fine except that, it is happening every second as the incoming DDE data is everrchanging and so the calculations always kick start off, every second.
This is making the program take lot of memory and computer resources is there any way to simplify this and yet make the calculations as precise as now? pls help


Code:

Private Sub Worksheet_Calculate()

'This is the code that decides wether to initiate the main program, _
by depending on the value typed in C30 in sheet Prima. _
Make sure that the value in C30 is not directly input but rather referenced to another cell this is in order _
to kick start the CALCULATE event in this case it is referenced to D30
If (Range("C30").Value = 1) Then

If (Range("C2").Value > Range("G2").Value) Then
Range("E2").Value = Range("C2").Value
Range("G2").Value = Range("C2").Value
Else
Range("E2").Value = Range("G2").Value
End If

If (Range("C2").Value < Range("H2").Value) Then
Range("F2").Value = Range("C2").Value
Range("H2").Value = Range("F2").Value
Else
Range("F2").Value = Range("H2").Value
End If

If (Range("C3").Value > Range("G3").Value) Then
Range("E3").Value = Range("C3").Value
Range("G3").Value = Range("C3").Value
Else
Range("E3").Value = Range("G3").Value
End If

If (Range("C3").Value < Range("H3").Value) Then
Range("F3").Value = Range("C3").Value
Range("H3").Value = Range("F3").Value
Else
Range("F3").Value = Range("H3").Value
End If

If (Range("C4").Value > Range("G4").Value) Then
Range("E4").Value = Range("C4").Value
Range("G4").Value = Range("C4").Value
Else
Range("E4").Value = Range("G4").Value
End If

If (Range("C4").Value < Range("H4").Value) Then
Range("F4").Value = Range("C4").Value
Range("H4").Value = Range("F4").Value
Else
Range("F4").Value = Range("H4").Value
End If

If (Range("C5").Value > Range("G5").Value) Then
Range("E5").Value = Range("C5").Value
Range("G5").Value = Range("C5").Value
Else
Range("E5").Value = Range("G5").Value
End If

If (Range("C5").Value < Range("H5").Value) Then
Range("F5").Value = Range("C5").Value
Range("H5").Value = Range("F5").Value
Else
Range("F5").Value = Range("H5").Value
End If

If (Range("C6").Value > Range("G6").Value) Then
Range("E6").Value = Range("C6").Value
Range("G6").Value = Range("C6").Value
Else
Range("E6").Value = Range("G6").Value
End If

If (Range("C6").Value < Range("H6").Value) Then
Range("F6").Value = Range("C6").Value
Range("H6").Value = Range("F6").Value
Else
Range("F6").Value = Range("H6").Value
End If

If (Range("C7").Value > Range("G7").Value) Then
Range("E7").Value = Range("C7").Value
Range("G7").Value = Range("C7").Value
Else
Range("E7").Value = Range("G7").Value
End If

If (Range("C7").Value < Range("H7").Value) Then
Range("F7").Value = Range("C7").Value
Range("H7").Value = Range("F7").Value
Else
Range("F7").Value = Range("H7").Value
End If

If (Range("C8").Value > Range("G8").Value) Then
Range("E8").Value = Range("C8").Value
Range("G8").Value = Range("C8").Value
Else
Range("E8").Value = Range("G8").Value
End If

If (Range("C8").Value < Range("H8").Value) Then
Range("F8").Value = Range("C8").Value
Range("H8").Value = Range("F8").Value
Else
Range("F8").Value = Range("H8").Value
End If

If (Range("C9").Value > Range("G9").Value) Then
Range("E9").Value = Range("C9").Value
Range("G9").Value = Range("C9").Value
Else
Range("E9").Value = Range("G9").Value
End If

If (Range("C9").Value < Range("H9").Value) Then
Range("F9").Value = Range("C9").Value
Range("H9").Value = Range("F9").Value
Else
Range("F9").Value = Range("H9").Value
End If

If (Range("C10").Value > Range("G10").Value) Then
Range("E10").Value = Range("C10").Value
Range("G10").Value = Range("C10").Value
Else
Range("E10").Value = Range("G10").Value
End If

If (Range("C10").Value < Range("H10").Value) Then
Range("F10").Value = Range("C10").Value
Range("H10").Value = Range("F10").Value
Else
Range("F10").Value = Range("H10").Value
End If

If (Range("C11").Value > Range("G11").Value) Then
Range("E11").Value = Range("C11").Value
Range("G11").Value = Range("C11").Value
Else
Range("E11").Value = Range("G11").Value
End If

If (Range("C11").Value < Range("H11").Value) Then
Range("F11").Value = Range("C11").Value
Range("H11").Value = Range("F11").Value
Else
Range("F11").Value = Range("H11").Value
End If

If (Range("C12").Value > Range("G12").Value) Then
Range("E12").Value = Range("C12").Value
Range("G12").Value = Range("C12").Value
Else
Range("E12").Value = Range("G12").Value
End If

If (Range("C12").Value < Range("H12").Value) Then
Range("F12").Value = Range("C12").Value
Range("H12").Value = Range("F12").Value
Else
Range("F12").Value = Range("H12").Value
End If

If (Range("C13").Value > Range("G13").Value) Then
Range("E13").Value = Range("C13").Value
Range("G13").Value = Range("C13").Value
Else
Range("E13").Value = Range("G13").Value
End If

If (Range("C13").Value < Range("H13").Value) Then
Range("F13").Value = Range("C13").Value
Range("H13").Value = Range("F13").Value
Else
Range("F13").Value = Range("H13").Value
End If

If (Range("C14").Value > Range("G14").Value) Then
Range("E14").Value = Range("C14").Value
Range("G14").Value = Range("C14").Value
Else
Range("E14").Value = Range("G14").Value
End If

If (Range("C14").Value < Range("H14").Value) Then
Range("F14").Value = Range("C14").Value
Range("H14").Value = Range("F14").Value
Else
Range("F14").Value = Range("H14").Value
End If


End If

End Sub





Hi

I have recorded the below code, is it possible to adjust this so that instead of using "USD" the macro will reference whatever currency the user inputs into say sheet1 cell A1?

thanks

Code:

"=IF(OR(RC[-3]=RC[-2],ISNUMBER(SEARCH(""USD"",RC[-1]))),""ok"")"





Hey guys,

My formula is as follows

Code:

=IF(OR((P75)="Undefined",ABS(O75)>$O$9,ABS(P75)>$P$9),"Yes","No")


The error is occuring when P75 is named as "Undefined" and I receive a #value! error. I would like it to yield a "yes" value. Any idea what might be off here? Thanks!

Edit: Title is a bit off now since I already found a way to make my divide by 0 errors show up as "Undefined". Now getting it to recognize "Undefined" in my OR function is the challenge.


I am trying to quantify 2 values ( -1 and 1) across 5 columns into usable data. I am trying to have the end result produce a number 5 through 1 in a blank column.

So if I am thinking about this correctly then I should have
Code:

If A2 = "1" Then
    F2 = "5"
ElseIf B2 = "1" Then
    F2 = "4"
ElseIf C2 = "1" Then
    F2 = "3"
ElseIf D2 = "1" Then
    F2 = "2"
ElseIf E2 = "1" Then
    F2 = "1"
End If


Which I translated to an Nested If of

Code:

=IF(A2="1",5,IF(B2="1",4,IF(C2="1",3,IF(D2="1",2,IF(E2="1",1)))))


All I am getting with the nested code is "False"

I am going to do another nested IF function to produce for the -1 and result in 1 through 5. Then I should be able to subtract the 2 values produced to get the information I am looking for.

Any help would be appreciated.

** EDIT **

Sorry I am working in Excel 2007 for this.


Just curious if I can Say Fill this Combobox with letters from A to AZ, so I do not need to do this:

Code:

Me.cobColumn.List = Array("A", "B", "C", "D", "E", "F", "G", "H", _
        "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", _
        "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", _
        "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", _
        "AY", "AZ")