Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Excel Web Query With Login And Password

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

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.

Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = ""
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


 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;
                        focusField = loginField;
            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("LOGON_ID").value;
                // alert(document.getElementById("forgedUsername").value);
                document.getElementById("forgedPassword").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) {
            function doAppContainerLogon() {
            	if ("/login"=="/j_security_check"
						|| "/login"=="/") {
            		// Manual validation of user passed, so container login module should succeed
            		// So, submit credentials.

Appreciate yur help.

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
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
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

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:

=C2&IF(SUM(D2:E2)>0," -","")
&IF(D2=3," on request","")
&IF(SUM(D2:E2)=2," or ","")
&IF(E2=3," on request","")
&IF(SUM(D2:E2)=2," on request","")
&IF(SUM(F2:O2)>0," contains (","")
&IF(P2=1," May Contain","")
&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(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(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(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(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(U2=1," May Contain","")
&IF(K2>0," Peanuts","")
&IF(AND(L2=1,M2=0,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(W2=1," May Contain","")
&IF(M2>0," Seeds","")
&IF(AND(N2=1,O2=0,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","")

Hi all,

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

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)


Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = ""
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.

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 = ""
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


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,


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?


Sub Login_WebQuery_SessionID()
Dim MyPost As String

Const MyUrl As String = "" '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


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,


Sub Login_WebQuery()

Dim MyPost As String
Const MyUrl As String = ""
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"



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



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;

=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..?



    ActiveCell.FormulaR1C1 = _
    ActiveCell.FormulaR1C1 = _

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


Sub Log()
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")
    ie.navigate ""
    ie.Visible = True
    Do While ie.Busy And Not ie.readyState = 4
    ie.document.all.Item("navbar_username").Value = "MyUser"
    ie.document.all.Item("Password").Value = "MyPass"
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)



A1 "5"
B1 ">" or "<"
C1 "2"
D1 "=IF(A1&B1&C1,"True","False")" not working, returning #VALUE! in D1. I notice that the evaluation appears to be (correctly) converting it to a string:


IF("5>2","True","False") 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!



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



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?



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

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

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

I tried the following in B1

=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:


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:



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


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

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:


Error too many Arguments

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

=(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.


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


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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Range("F14").Value = Range("H14").Value
End If

End If

End Sub


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?




Hey guys,

My formula is as follows



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

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



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:


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")