Posts by Robert B

Re: Get Record Creation Datestamp

Hi

In Table Design view, create a field "CreationDate" or similar, set the format to Date/Time and in the Default Value in Field Properties type =Now().

This will enter the dat of new record entries and can be queried in the usual way.

I am not too clear of the last part of your question, but hope that this is a start

Robert

Re: Last Cell Used In Column When Rows Are Hidden

Hi

try

Range = ActiveCell.SpecialCells(xlCellTypeLastCell).Select

HTH

Robert

Re: Constants Are ($) Variables Are?

Hi

If you mean the $ in, e.g. $A$1, any formula copied down will not increment the cell reference. The sign for variables is no sign, i.e. remove the $ signs.

HTH

Robert (Too Slow)

Re: Collating Data From Multiple Spreadsheets In A Specific Folder

this code is from Dave's For Loop tutorial, whcih you could modify to suit

Sub RunCodeOnAllXLSFiles() 
    Dim i As Integer 
    Dim wbResults As Workbook 
    Dim wbCodeBook As Workbook 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.EnableEvents = False 
     
    On Error Resume Next 
     
    Set wbCodeBook = ThisWorkbook 
     
    With Application.FileSearch 
        .NewSearch 
         'Change path to suit
        .LookIn = "Type your path here" 
        .FileType = msoFileTypeExcelWorkbooks 
         
        If .Execute > 0 Then 'Workbooks in folder
            For i = 1 To .FoundFiles.Count 'Loop through all
                 'Open Workbook x and Set a Workbook variable to it
                Set wbResults = Workbooks.Open(.FoundFiles(i)) 
                 
                 'Do Your Code Here
                 
            Next i 
        End If 
    End With 
     
    On Error Goto 0 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    Application.EnableEvents = True 
End Sub

Robert

Re: Customizing Query

Hi

What is it that you can't get to work, is it the query or the macro?

Robert

Re: Customizing Query

Hi

Welcome to the forum

To achieve the first objective your query will need to select totals and apply min to the first date and max to the second.

Having done this choose the SQL view of the query and paste that into a macro, remembering to delete the ; at the end and enclosing the string in "s.

HTH

Robert

Re: Copying A Variable Range Of Cells

Hi

Welcome to the forum

the following will select the area from any cell

dim myRange as range


set myRange = Range(ActiveCell,ActiveCell.Offset(32,11))

HTH

Robert

Re: Checking date in cell A1 Monday or not

Hi

try something like, replacing the message boxes, as required

Dim n As Integer


n = Weekday(Range("A2"), vbMonday)
If n = 1 Then
MsgBox ("Monday")
Else
MsgBox ("Not Monday")
End If

HTH

Robert

Re: Pivot Table For Large Amounts Of Data

Hi

I would certainly be interested in your resolution of the problem and, just as a matter of interest, how there came to be so many columns, given that 3 figures a month results in 36 columns per year, 800 would hold over 20 years data.

Anyway, glad it's solved.

Robert

Re: Depending Variables

Hi

In the sample below t will have the values you want

Dim y As Integer, t As Integer, n As Integer


For y = 2 To 47 Step 9
t = y + n
'do t based stuff here
n = n + 1
Next y

HTH

Robert

Re: Pivot Table For Large Amounts Of Data

Hi

I can't seem to get around this one. The only way I can see would be to reorganise your data so the each Row contains these Column headings

Project CostCentre Year Month A/B/F Total

If you think this is a possibility, a macro could be written to manage the reorganisation

Sorry not to be of more help

Robert

Re: Macro For Switching Autofilter Between Values

Thank you, shg, for your assistance

Robert

Re: Macro For Switching Autofilter Between Values

Hi

I guess the simplest method would be to identify the value of the last visible cell and use an IF statement to set the critical value to the other value, something like

Dim CritVal as string


CritVal = ActiveCell.Offset(xlDown) ' or however you prefer


If CritVal = "SB" Then CritVal ="SE8"
Else
CritVal ="SB"
End if
selection.AutoFilter Field:=1, Criteria1:= CritVal 'change Field to suit

HTH

Robert

Re: Pivot Table For Large Amounts Of Data

Hi

could you post a sample of the worksheet. As you know Pivot Tables work on columns, and it seems at first glance that the data is transposed, i.e. the data that should be in columns is in rows. It may be possible to transpose the data, enter some column headings and pivot the data as required.

Robert

Re: Problem With Auto Filter And Date

Hi

does this still happen after you have applied the Text to Columns function?

If so, it would help to post a sample of the file that is being imported.

Robert

Re: Problem With Auto Filter And Date

Hi

Welcome to the forum.
I suspect the problem may be that when a text file is imported the columns containing what appear to be dates, are, in fact text with no date format set. As a first step import the text file manually and then apply the Text to Columns function (select Data on the Toolbar then Text to Columns). You should then be able to apply the date format you need.

HTH

Robert

Re: Variable Selection Delete

Hi

sorry about that, try

Rows("7" & ":" & N).Select

Robert

Re: Variable Selection Delete

Hi

try inserting at the start of the macro

Dim N as integer
N = InputBox("Enter Row Number") - 1

and then replace

Rows("7:13").Select

with

Rows("7":N).Select

HTH

Robert

Re: Formating A Cell To Number

Hi

It would seem unlikely that the number being evaluated is other than a whole number, this is an alternative

Range("D11").NumberFormat = "0"

robert

Re: Tying Two Documents Together

Hi

what is the formula you have in the summary workbook?

It should look something like

=[OriginalWorkbook]Sheet1!$B$1

Robert