Re: nested if statement with 2 and's
THanks. Go Rabbitohs.
Re: nested if statement with 2 and's
THanks. Go Rabbitohs.
Can someone help with this: =IF(AND(C4=3,U15=3),"China",AND(C4=2,Q15=2,U15=2),"Gladstone","") thanks.
Re: if statement based on background color in range of cells
oK thanks.
Re: if statement based on background color in range of cells
Can you please check again KrishnaKumar. When I put on zeros in that range, which changes the cell colour from red, it comes back as true instead of false.
Re: if statement based on background color in range of cells
forum.ozgrid.com/index.php?attachment/54169/ Thanks Krishakumar The if statement above is coming back with an error. Can you have a look at the attached workbook. If statement is in cell I55.
Re: if statement based on background color in range of cells
Thanks cytop.
Can you give an example of how the statement would look e.g.
=IF(CellColour(T18:T38)=35,"Red","Not Red")
Hi Guys.
I am using the following function:
Function CellColour(Irow As Integer, Icol As Integer) As Long
CellColour = Cells(Irow, Icol).Interior.colorIndex
End Function
and the following if statement:
=IF(CellColour(20,18)=35,"Red","Not Red")
This works well for one cell but I need a function that will cover the range T18:T38. The if statement will be ' if any cell in that range has the background color red(35),True, False.
Thanks in advance.
Re: Run-time error '75'
Your right.
It was with that line.
The code saves to two directories and one of those did not exist. Stupid mistake.
Thanks.
Re: Run-time error '75'
OK,thanks.
Hello. When I run the bellow code I get a Run-time error '75': Path/File access error
Before this error comes up the file is actually saved in the correct directory. Also, this code runs fine on other computers using the same software.
I have run a repair with the Office installation disk, which did not help. I am thinking that I should reinstall office to see if that works.
The blue text in the code is what is highlighted when I debug.
Suggestions please?
Sub ArchiveFile()
If Sheets("Prep").Range("B1") = "" Or Sheets("Prep").Range("B5") = "" Or Sheets("Drafts").Range("F9") = "" Then
MsgBox ("Check Ship\Surveyor\Initial stop date cells are not empty")
Exit Sub
End If
On Error GoTo ErrorHandler
ChDir "C:\Dropbox\Archive\" & Range("C79")
ActiveWorkbook.SaveAs "C:\Dropbox\Archive\" & Range("C79") & "\" & Range("B79") & ".xlsm", 52
ActiveWorkbook.SaveCopyAs "C:\Current Ships\" & Range("B79") & ".xlsm"
Exit Sub
ErrorHandler:
MkDir "C:\Dropbox\Archive\" & Range("C79")
Resume Next
End Sub
Display More
Re: 2 dimensional look up
Thanks for your reply.
I have expanded the table with the attached. Using the function you provided, with some times I am getting the incorrect value returned. The table values being returned for 12:00 are for 11:45. I changed the match_type for the time value from 1 to 0, which gave me a N/A value. I copied and pasted the 12:00 from the time row and this returned the correct value. For some reason when I type in the number the cells are'nt matching up. Again I checked the formatting with no issues. I also named the ranges as you said. Could you please have a look at the attached to see where I am going wrong. Thanks.
[ATTACH=CONFIG]51550[/ATTACH]
Please see the attached.
I am using a function to find a value in the table based on the column date value in C22 and row time value in C23. Sometimes the correct value is returned (eg. if I enter 0:30 in C23) and sometimes N/A is returned (eg. 1:15).
Excel is saying that the Match part of the function is the problem. I have checked the formatting and the values seem to be the same. I have played around with the match function but cannot solve the problem. Hopefully someone can help. THanks in advance.
Re: Range not appearing in ListBox
Yes. As explained above I have changed the properties and that fixed the problem. Thanks.
Re: Range not appearing in ListBox
Sorry. Have edited my last post and have also worked out the problem by changing the number of columns in the listbox properties.
Hi. Please see attached workbook. forum.ozgrid.com/index.php?attachment/50295/
I would like a macro to:
Find the cell with the first date value in Column A based on the cell value in J1.
Select a range from the cell that was found in Column A, 16 rows down and 7 Column across.
Then I will copy the selected range and paste in a specific cell (I think I know how to do that.)
Thanks.
Re: Drag formula down column without changing cell reference
thanks smallman.
Hi.
In cell D3 I have the formula =(Sheet1!B16/2)-F3. When I drag this formula down the column, how do I get the value of Sheet1!B16 in the formula to stay the same?
The value in column F changes from F3 to F4, F5, F6 and so on, which is what I want to happen.
I cannot just use the actual value of Sheet1!B16 as it changes from one file to the next and I want to set up a macro that will run the formula.
Thanks
Re: macro to insert current date with specific format
Works for me too. Thanks a lot Roy.