time frame restriction

[jzamilpa3]

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

 

[Keith74]

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

 

[jzamilpa3]

it starts working correctly in the beginning but it gives a type
mismatch error for the code you helped me out with. any suggestions

 

[Keith74]

check the data its failing on, it's probably something that its having
problems converting to a date.

 

[jzamilpa3]

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

 

[jzamilpa3]

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

 

[Keith74]

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

 

[jzamilpa3]

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("A91000").Select
Selection.Clear
Selection.WrapText = True

 

[Keith74]

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("A91000").Clear
ThisWorkbook.Sheets("Sheet12").Range("A91000").WrapText = 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