Hi, I am pretty new to this and just tinkering around with various
searchs to try to piece together a working way of taking audit data and
putting it into a printable / legible document. I have gotten
everything to work to this point except for the one vital final piece.

I have three worksheets: main, second, and filter. The filter worksheet
is where all the information is formatted. What has to happen then is
the audit date (day value of that I want) to use to format a range of
data on main and second.

For Example:

Audit Date ='s November 1,2006
I would like the 1 in the audit date to be set to y (not sure how), so
that y can be used in my next formula of:
sheets("Main").cells(x,(y-1)).value = sheets("Filter").Cells(x,4).value
with a loop in there to loop through it 49 times for main +1 increments
(which I figure a For and Next is sufficient. The reason I want it to
do it based on date is so that it will auto update upon opening based
on the audit date in the file so there is no user error possible.

My problem lies with just converting the day from date to a value that
can be used in such a way...

Any assistance would be greatly appreciated. I get confused easily when
you use your technical jargon so of you can give me a description of
how or why it works that would be great also.

Have you tried something like this?

y = TEXT(A1,"d")
(Changing A1 to the actual cell the date is in.)

Best wishes,

Jim
--
Best wishes,

Jim


"(E-Mail Removed)" wrote:

> Hi, I am pretty new to this and just tinkering around with various
> searchs to try to piece together a working way of taking audit data and
> putting it into a printable / legible document. I have gotten
> everything to work to this point except for the one vital final piece.
>
> I have three worksheets: main, second, and filter. The filter worksheet
> is where all the information is formatted. What has to happen then is
> the audit date (day value of that I want) to use to format a range of
> data on main and second.
>
> For Example:
>
> Audit Date ='s November 1,2006
> I would like the 1 in the audit date to be set to y (not sure how), so
> that y can be used in my next formula of:
> sheets("Main").cells(x,(y-1)).value = sheets("Filter").Cells(x,4).value
> with a loop in there to loop through it 49 times for main +1 increments
> (which I figure a For and Next is sufficient. The reason I want it to
> do it based on date is so that it will auto update upon opening based
> on the audit date in the file so there is no user error possible.
>
> My problem lies with just converting the day from date to a value that
> can be used in such a way...
>
> Any assistance would be greatly appreciated. I get confused easily when
> you use your technical jargon so of you can give me a description of
> how or why it works that would be great also.
>
>

When I use the formula you entered I get an object not defined error
with TEXT being the object. What am I defining TEXT as? It appears that
Y should be defined by using Text format but if I go with Format.Text
"d" I get a 400 error with a 0 value in the cell so it will not
continue to parse and give me the results in the column.

I need y to be an integer?
I'm not sure what defining as long or double does although when I set
it at a consistent value it works fine but won't obviously post into
the correct column because it is not defined by the date. If that makes
any sense.

I'll post the entire code that I am using below and perhaps someone can
help me figure this out. I am sure it is probably the first lesson in
vba 101 but I can't find that lesson

==================================================== Project is here:

Private Sub Workbook_Open()
Fix_It
End Sub


Sub Fix_It()

Dim x As Integer
Dim S As Worksheet
Dim Y As Integer
Dim A As Date
Dim Z As Integer
Dim T As String

' Macro recorded 11/4/2006 by Darcey
'
' Application.ScreenUpdating = False

'
' Error Control

Dim varAnswer As String

varAnswer = MsgBox("Data from PDF has been copied?", vbYesNo,
"STOP!")
If varAnswer = vbNo Then
End
End If

' Set Scroll area of Worksheets

' Sheets("Filter").ScrollArea = "A1110"
' Sheets("Main").ScrollArea = "B3:AF52"
' Range("B3").Select
' Sheets("Second").ScrollArea = "B3:AF64"
' Range("B3").Select

' Set Filter as Active Sheet for Data Formatting

Set S = ActiveWorkbook.Sheets("Filter")
Worksheets("Filter").Activate

' Clears the current workspace

Cells.Select
Selection.ClearContents

' Sets the Worksheet default selection to the first cell

Range("A1").Select

' Paste Clipboard Data into the Worksheet

ActiveSheet.Paste

' Format the columns

ActiveCell.Columns("A:A").EntireColumn.Select
Selection.TextToColumns Destination:=ActiveCell,
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1)), _
TrailingMinusNumbers:=True

' Copy the Audit Date Value

Range("C2").Select
A = Selection.Value


' Remove non numeric rows entirely for easier formatting

On Error Resume Next
Range("a:a").SpecialCells(xlBlanks, _
xlTextValues).EntireRow.Delete
Range("a:a").SpecialCells(xlConstants, _
xlTextValues).EntireRow.Delete
Range("a:a").SpecialCells(xlFormulas, _
xlTextValues + xlErrors + xlLogical).EntireRow.Delete
On Error GoTo 0

' Attempt to Ensure First Floor Data is 1001 to 1049
Y = 1
Z = 1001

Do Until Z = 1050
If Cells(Y, 1).Value <> Z Then
Cells(Y, 1).EntireRow.Select
Selection.Insert Shift:=xlDown
Cells(Y, 1).Value = Z
End If
Y = Y + 1
Z = Z + 1

Loop

' Attempt to Ensure First Floor Data is 1001 to 1049
Z = 2001
Do Until Z = 2062
If Cells(Y, 1).Value <> Z Then
Cells(Y, 1).EntireRow.Select
Selection.Insert Shift:=xlDown
Cells(Y, 1).Value = Z
End If
Y = Y + 1
Z = Z + 1
Loop

' Attempt to put sums of column 2 and 3 into column 4

x = 1
Do While Cells(x, 1).Value <> ""
Cells(x, 4).Value = Cells(x, 2).Value + Cells(x, 3).Value
x = x + 1
Loop

' Audit Date Setup

Range("E1").Value = "Audit Date:"
Range("G1").NumberFormat = "mmm-yyyy"
Range("G1").Value = A
Range("F1").NumberFormat = "d"
Range("F1").Value = A
Q = Text(F1, "d")

' Control Point before proceeding to append data into main sheets

varAnswer = MsgBox("The Procedure is for Audit Date: " + T + " Is
this Correct?", vbYesNo, "Warning!!!!")
If varAnswer = vbNo Then
End
End If

' Need to now add a control to auto post to correct sheet 'main or
'second
' Based on the date provided in Range F1
' Seperating by floor.
' sheet labels are as follows...
' main - second - filter (Filter being the active sheet).
' Begin Appending the Information between the two sheets based on the
audit date...

For x = 3 To 51
Sheets("main").Cells(x, (Q + 1)).Value =
Sheets("Filter").Cells((x - 2), 4).Value
Next x

For x = 3 To 63
Sheets("Second").Cells(x, (Q + 1)).Value =
Sheets("Filter").Cells((x - 2), 4).Value
Next x

' Error Control

End Sub


Jim Jackson wrote:
> Have you tried something like this?
>
> y = TEXT(A1,"d")
> (Changing A1 to the actual cell the date is in.)
>
> Best wishes,
>
> Jim
> --
> Best wishes,
>
> Jim
>
>
> "(E-Mail Removed)" wrote:
>
> > Hi, I am pretty new to this and just tinkering around with various
> > searchs to try to piece together a working way of taking audit data and
> > putting it into a printable / legible document. I have gotten
> > everything to work to this point except for the one vital final piece.
> >
> > I have three worksheets: main, second, and filter. The filter worksheet
> > is where all the information is formatted. What has to happen then is
> > the audit date (day value of that I want) to use to format a range of
> > data on main and second.
> >
> > For Example:
> >
> > Audit Date ='s November 1,2006
> > I would like the 1 in the audit date to be set to y (not sure how), so
> > that y can be used in my next formula of:
> > sheets("Main").cells(x,(y-1)).value = sheets("Filter").Cells(x,4).value
> > with a loop in there to loop through it 49 times for main +1 increments
> > (which I figure a For and Next is sufficient. The reason I want it to
> > do it based on date is so that it will auto update upon opening based
> > on the audit date in the file so there is no user error possible.
> >
> > My problem lies with just converting the day from date to a value that
> > can be used in such a way...
> >
> > Any assistance would be greatly appreciated. I get confused easily when
> > you use your technical jargon so of you can give me a description of
> > how or why it works that would be great also.
> >
> >