HTML Code:
If Left(Range("H18"), 7) = A23C567 Or Left(Range("H18"), 7) = A65C321 Then
ActiveSheet.Cells(6, 7).Value = "Business and Private Banking"
End If
Im trying top use the above bit of code and it does not work when the criteria is Text , so if I simply change the above to =1234567 it works fine -- But I need it to be alpha numeric
im trying to use below formula using SUM and two IF conditions and 1 left formula
Cond 1
Cond 2
Value
22
m9
10
23
m9
10
[code]....
the following formula works to capture the sum of combination 22 and m9:
{=SUM(IF((A2:A6)=A8,IF(B2:B6=B8,C2:C6,)))}
22
m9
20
24
m8
10
however, in my original table, the 22, 23 etc are values with 6 numbers as shown below
Cond 1
Cond 2
Value
2201
m9
10
2301
m9
10
[code]....
following formula didnt work;
{=SUM(IF(LEFT(E2:E6,2)=E8,IF(F2:F6=F8,G2:G6,)))}
22
m9
0
23
m9
0
why the secund formula didnt capture anything when i added left to the first one.
i checked and checked and my IF function just gives me the wrong answer... attached is just a sample data..i have over 230 lines to check actually..
View 4 Replies View RelatedI am having issues with a min if formula, even though i enter it as an array (ctrl +shift+enter), it only returns a zero.
The formula is supposed to return the earliest date from Raw data if the name of the company matches the one from the data validation ('Company lookup'!D3).
Dates are in column D and company name in column M
I checked, the dates are all formatted as dates
=MIN(IF(RAW!$M:$M,'Company lookup'!D3,RAW!$D:$D))
I'm coping and pasting data (html) into a worksheet. When I try to format some of the columns as accounting$, number, ect. They won't format. It looks as if there are spaces before and after the data. So I tried the trim and clean function but it doesn't work, so I guess the blank "spaces" are not really spaces.
It gets better...
When I do the same exact copy and paste (as html) on another persons computer, they are able to format just fine, and the "spaces" are removed automatically.
Why is this working on one computer but not the other? Is it a setting that my computer has enabled or disabled? If so, what is it?
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within.
For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found.
My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.
I'm using Excel 2007. When I try to scroll with the mouse wheel, it doesn't do anything. If I hold down the control key and scroll with the mouse, it zooms in and out. So that works fine, but I can't do the basic scrolling up and down the document with the mouse wheel. There is no "Tools, Options" menu in 2007 so I don't even know where to find this type of option. The options available from the Office button are completely different.
View 4 Replies View RelatedI have a spreadsheet with data similar to the following:
12111000 MILK, COW'S, FLUID, LOW PERCENT
27313010 BEEF, NOODLES & VEG (W/ CARROTS/DK GREEN), NO SAUCE
11100000 MILK, NFS
11111000 MILK, COW'S, FLUID, WHOLE
If I use the following code to pass in a string variable and then attempt to find the string it never sees the text in line 2; or any line that contains (). The code is simply a test to try and figure out what i'm doing wrong. The actual goal is to search the entire sheet for text similar to that entered in by the user and then copy every row that contains similar text into another worksheet.
Sub CopyStuff()
strVariable = "car" 'I have tried "*car*" also
With Worksheets(1). Range("b1:b500")
Set c = .Find(strVariable, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Set c = .FindNext(c)
MsgBox (test)
Loop While Not c Is Nothing And c.Address <> firstaddress
test = c.Address
End If
End With
End Sub
Anyways, the issue is that when I have some cells selected then use the text-to-columns, it works fine.. but when I select an entire column, it "acts a fool"...
I made this video to show the effect on my computer.
I can't get the text box function to work on this spreadsheet.
View 2 Replies View RelatedI have a hyperlink to [url] that doesn't work. When I click it I get an error saying "Unable to locate the server or proxy server". But when I cut & paste the address from the hyperlink into my browser (no chance of mistyping), it works fine.
View 11 Replies View RelatedAs said, the right click doesn't work anymore, last week it was working but it doesn't anymore. When I right-click wherever in the spreadsheet nothing happens but I know it's working because it works outside excel.
View 9 Replies View RelatedSee the excel sheet [URL]
I need to delete all the digits before / and also the / - in the valuta columns. But this =RIGHT(B2,LEN(B2)-6) doesnt work It says > the formula you typed contains an error
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?
Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.
i got a long macro to delete all rows with contains a column with the value 'xx'. The macro works in sheet 2K, 2F and 1G. But the weird thing is it doesnt work in sheet 1K (i copied the exact same part of the macro out of the other sheets). Is it possible that there are to many rows in the sheet (sheet 1K has 24.000 rows, 50% or more will be deleted with the macro. The error says bug in the line: If Not rng Is Nothing Then rng.EntireRow.Delete It stops showing all the rows wich should be deleted.
Sub GrondeigenarenVerwijderen()
Application.Goto ActiveWorkbook.Sheets("2K").Cells(1, 1)
Dim DeleteValue As String
[Code]....
I have below macro for Sheet2 and i have created a button on Sheet1.now whenever i press this button the macro will run and save Sheet2 as PDF file.
The problem is when i hide Sheet2 the macro doesnt work and it gives an error " Invalid procedure call or argument.
how to make this macro run even when the sheets are hidden ?
Sub PDF_Table()
'Sheet2.PageSetup.PrintArea = "$a$1:$x$140" '*****
'*** can remove the above line if sheet areas are already set and will not be altered
With Sheet2.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Zoom = 60
[code]...
I can't get this to work. What am I doing wrong? I keep getting a #VALUE! error.
=SUMIFS($G$2:$G$31,$L$2:$L$30,F37,$B$2:$B$30,G$36)
I want to sum the NUMBERS in golumn G if the DATE in column L matches the DATE in cell F37 AND if the TEXT value in column B is equal to the TEXT value in cell G36.
The answer is probably "because I'm stupid", but I really can't get my head around it! I'm playing with variant arrays for the first (and possibly last) time,
The code I have is:
Sub test()
Dim vSheetColours As Variant
Dim iCounter As Integer
vSheetColours = Range("Colours").Interior.ColorIndex
For iCounter = 1 To UBound(vSheetColours, 1)
MsgBox vSheetColours(iCounter, 1)
Next iCounter
End Sub
(Obviously this code doesn't do anything useful - but if I could get it to work, I might have a chance of making my real code work!)
Colours is a range of 8 cells. Each one has some text in, and has a different background colour. I'm trying to store the colours.
If I run this code, I get a runtime error 13 type mismatch, and it highlights
For iCounter = 1 To UBound(vSheetColours, 1)
But if I replace
vSheetColours = Range("Colours").Interior.ColorIndex
with
vSheetColours = Range("Colours").Value
it works fine.
This might be extremely easy, but why my Excel (nor Word for that matter) doesn't go to full screen anymore? I'm pretty sure that I haven't used any options, I do have a new graphics card, but all the other applications goes to full screen. I have restarted my computer. Basically I think that it's either some option, or then reinstalling Office.
View 9 Replies View RelatedI have a programme which works fine on my computer at home but when I send it via email to work the 'sort' function messes up (there is a chance it could be the 'vlookup' function, though) and all the cells in the sort range lose their formula reference - which is of course a tad annoying.
The part of the programme I'm having difficulty with is this:
Range("SsNo1").Resize(d, f).Offset(a, 0).Select
Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Why doesn't Cancel (Escape key) work while Sleep API (kernel32.dll) is running?
I was looking at making a minor change to a solved thread (see [URL] ....)
I would like the user to be able to exit the form by pressing Escape. I altered the start and end of the sub to below but it didn't work:
START:
[Code]....
END:
[Code] .....
I always used Office 2003 .. I just installed Office 2007 to try it out. But everything doesn't work in Excel 2007, which does work in Excel 2003.
I added an example of the list, but here are the codes:
Sheet1
I have a workbook that I transfer back and forth from my desktop to my laptop, that uses Sendkeys to add code to the active pane in VBA. It doesn't work on my laptop, but works fine on my desktop.
Both computers have Vista. Is there a switch somewhere to make Sendkeys work?
I'm not sure why this macro in workbook "A" is not working when checking for the activesheet sheet opened in workbook "B"? I've included the tab name I'm looking for and the tab name opened in the workbook "B" in the MsgBox to verify it can pull the tab names properly in workbook "B" and it returns correctly. I'm not sure why the If Then Else is not working if that is the issue here.
Code:
Sub testmonthcheck()
Dim art156 As Workbook
Dim mm As String
Dim mmm As String
Set art156 = Workbooks("Actual_Run_Times_156.xls")
[Code] ........
Why doesn't a three column formula work?
Column C is Credit, D is Debit, E is Balance
I put the following formula in the Balance column
=E21+C22-D22
where the first term refers to cell immediately above. In other words, this formula takes the prior balance, adds a credit if any, subtracts a debit, if any, and gives a new balance.
The formula worked on the cell I put it in. It will not work when I copy and pasted it into the cell below the first one. Error I get is circular reference.
So, I'm trying to come up with a dynamic graph title. I highlight the graph title, go to the formula bar, and type:
Code:
=CONCATENATE(Sheet1!$C$1," All Servers")
"That function is not valid". Yet, the same function, placed in a cell, works perfectly. I suppose I could use an intermediate cell to hold the concatenated text and point the graph title to that cell, but I dislike having contextless cells like that floating around. Is there a way to apply that formula, or some other with the same effect, directly to the graph title?
I'm trying to create a code that autofills the next empty row in colum A with a number. What I have at the moment is:
'Create Index in Transaction list
x = Range("A" & Rows.Count).End(xlUp).Value
Sheets("Transaction list").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = x + 1
If I run this code in isolation from the rest of my code (which is copying and pasting values from one sheet to another) it works, but when I run the full code it only autofills the rows with "1".
I found the thread about how to get the second best result (=LARGE(range,2)) but that can't handle when I have ywo top values and a lower third value. It still return the next value after the top value, which is the same value. I want to get the next lowest vaule.
Example:
In this row:
7
7
6
5
5
4
I want to get the value 6 and the formula =LARGE(range,2) gives the second number 7.
How do I get the second value that I want?
Maybe if I use the RANK() formula in some way, but how?
I'm trying to simplify a formula that I'm using in a spreadsheet that's being used mostly in Excel 2000.
2 reasons I'm doing this:
1) It doesn't work if the worksheet hasn't been saved yet.
2) It's ugly
=DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2)))+1,0)
The formula returns the last day of the month of the name of the sheet. For example, if the sheet is names "Mar 07", it returns 3/31/07. (yeah, when the 2100 issue becomes a big deal, I'm not going to be ready but I have 92 years to think about it)
Even if I can't make it prettier, does anyone have a way to get the sheet name into a formula without VBA Code in a file that hasn't been saved.
I am trying to run a macro in the background. I have added the Application.ScreenUpdating = False (and True at the end) to my code but no luck.
View 9 Replies View Related
