Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
time frame restriction
the restriction or known as IF is suppused to look at 2 cells to get
a date time frame. cell1 is 6/1/2007 and cell2 5/31/2008. i need it to look between that time frame or any dates inserted into those 2 cells. evrything works correctly except the IF and ENDIF For a = 2 To 500 'If Sheet2.Cells(a, 2) = Sheet12.Cells(6, 3) And Sheet2.Cells(a, 2) <= Sheet12.Cells(6, 4) Then strBlah = Sheet2.Cells(a, 7) Sheet12.Cells(g, 1) = strBlah Sheet12.Cells(g, 2) = Sheet2.Cells(a, 15) Sheet12.Cells(g, 3) = Sheet2.Cells(a, 4) & " " & Sheet2.Cells(a, 9) & " " & Sheet2.Cells(a, 11) & " Phn#:" & Sheet2.Cells(a, 12) Sheet12.Cells(g, 4) = Sheet2.Cells(a, 17) g = g + 1 'End If Next a |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
time frame restriction
Hi Jzamil
Dates are one big problem in excel, you need to be certain which date format you're working with e.g. is it 01/02/2008 or 02/01/2008, so step through your code and make sure. I'd replace your IF with If cdate(Sheet2.Cells(a, 2)) = cdate(Sheet12.Cells(6, 3)) And cdate(Sheet2.Cells(a, 2)) <= cdate(Sheet12.Cells(6, 4)) Then to confirm that excel is treating the values as dates, though you may be better off using .NumberFormat. hope this gets you started. Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
time frame restriction
it starts working correctly in the beginning but it gives a type
mismatch error for the code you helped me out with. any suggestions On May 13, 8:19*am, Keith74 wrote: Hi Jzamil Dates are one big problem in excel, you need to be certain which date format you're working with e.g. is it 01/02/2008 or 02/01/2008, so step through your code and make sure. I'd replace your IF with * * If cdate(Sheet2.Cells(a, 2)) = cdate(Sheet12.Cells(6, 3)) And cdate(Sheet2.Cells(a, 2)) <= cdate(Sheet12.Cells(6, 4)) Then to confirm that excel is treating the values as dates, though you may be better off using .NumberFormat. hope this gets you started. Keith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
time frame restriction
check the data its failing on, it's probably something that its having
problems converting to a date. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
time frame restriction
On May 13, 8:34*am, Keith74 wrote:
check the data its failing on, it's probably something that its having problems converting to a date. Column B where its checking the dates from are all in this format 07/23/2007 and keeps doing the mismatch error |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
time frame restriction
On May 13, 8:41*am, wrote:
On May 13, 8:34*am, Keith74 wrote: check the data its failing on, it's probably something that its having problems converting to a date. Column B where its checking the dates from are all in this format 07/23/2007 and keeps doing the mismatch error ok i found the problem and it work fine now. one quick question. how do i make a specific column one color and with borders i have 4 columns and column D is the one i need with |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
time frame restriction
Highlight the required area, right click and its under format cells,
you want the border and patterns tabs, to get the vba, record a macro while doing this. hth keith |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
time frame restriction
cant i just add on to this. i tried rcording a macro then copy and
paste the code to here and it wouldnt work. Sheet12.Range("A9:D1000").Select Selection.Clear Selection.WrapText = True |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
time frame restriction
Unless you have an object defined as Sheet12 it won't :)
I'm guessing you need something along the lines of ThisWorkbook.Sheets("Sheet12").Range("A9:D1000").C lear ThisWorkbook.Sheets("Sheet12").Range("A9:D1000").W rapText = True Sheet12 = the name or index position of the sheet you want to change. It's always best to avoid select and activate unless you really need them, slows the code down. hth Keith On 13 May, 15:56, wrote: cant i just add on to this. i tried rcording a macro then copy and paste the code to here and it wouldnt work. Sheet12.Range("A9:D1000").Select Selection.Clear Selection.WrapText = True |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Frame | Excel Discussion (Misc queries) | |||
data time frame | Excel Programming | |||
Time limit restriction. | Excel Programming | |||
ask for overtime with time restriction | Excel Discussion (Misc queries) | |||
3 Time frame | Excel Worksheet Functions |