Posts by james.aldcroft

Re: Userform To Display/modify A Stock List

No worries. I couldn't see the spreadsheet functions you referred to being overwritten though - can you point them out.

Also you said the result was supposed to be returned via user form, where are you returning these results?

Cheers,

Jim

Re: Comma's Purpose In Recorded Macro

Change your code flags to [/code] and it should be easier to track down.

Cheers!

Also can you highlight the particular line - lots of commas in there! Cheers,

Re: Userform To Display/modify A Stock List

Can you attach the spreadie?

Added By Admin

The default Proptery of a TextBox is Text. Always use TextBox1.Value and numbers are seen as numbers and text as text.

Re: Toggle Cell Format Between Two States

Try this:

Sub JimTest()


    With Selection


        If .Font.ColorIndex = 1 Then


            .Font.ColorIndex = 2
            .Interior.ColorIndex = 1


        Else


            .Font.ColorIndex = 1
            .Interior.ColorIndex = 2


        End If
    End With
End Sub

Hello chaps,

I've been working on a db for one of my mates who's starting up a business, but I haven't really used the 'reports' part of Access.

I assume it would be possible to produce a purchase order style report, but I can't work out how to do it. I have two tables relevant for creating the PO - order and order details.

Basically, I want to select an order and generate a PO (showing the relevant order details). Is this possible using 'reports' or will it be necessary to fall back to VBA?

Cheers,

Jim

Re: Hide All But Selected Cells

Andy,

Your Excel skills are surpassed only by your personal good looks and charm! :p

Thanks a million,

Jim

Hi Chaps,

I'm probably being incredibly dim, but I can't figure out a way to do this easily:

Basically, I want to be able to select a range on my spreadsheet, click a button, and have everything that is not selected hide itself.

I've tried using intersect, but I'm not sure how to (quickly) loop through all of the columns and rows to see if my range is contained within.

Has anyone got any ideas?

Thanks in advance.

Jim

Hi,

I've encountered a bit of an odd one. I am opening a tab delimited file, which has been sved as an xls.

When I open from file manager, it opens correctly. When I open from Excel, I have to go through 3 screen dictating how the data is set up, and the end result is that a couple of the numbers come through as text, rather than numbers.

Does anyone know why?

Failing an explanation - how do you open workbooks as file system objects - currently I am opening them using

Workbooks.Open Filename:=wb.Path & "/" & ws.Range("B1").Value

If I open them by mimicking the FM, perhaps it will work OK.

Thanks in advance,

Jim

Re: Find Method, Searching Space Starting After A Few Cells

Hi Nathalie,

You don't have a continuation charachter on the top line of your statement. It should read:

Dim rFoundCell As Range 
Dim rFilledCol1 As Range 
 
 'I define the column excluding the cells that contain empty cells
rFilledCol1 = Columns(1) - Range("A1:A10") 
Set rFoundCell = rFilledCol1.Find(What:="", After:=Range("A11"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ 
SearchDirection:=xlNext, MatchCase:=False)

Cheers,

Jim

Re: Update a worksheet every second (or less)

Fantastic,

This works like a charm.

Thanks for your help.

Jim

Re: Update a worksheet every second (or less)

Certainly - it's all in Excel (No VBA).

Thanks.

Hi,

I'm not sure whether I need to use VBA to get the desired result, or whether I can just use something in Excel.

I've written a worksheet to countdown to a specified time - however, unless a cell value is changed or a manual calculation (F9) is forced then the current time does not update.

Does anybody have an idea as to how I could get my clock to tick?

Thanks,

Jim

Re: Inserting column and dragging down formulas

I'm not sure this is exactly what you want - this inserts an new row where you select a cell, and then copies the original row into the row you are inserting into.

So formulae will be copied down - Is this what you wanted?

Sub CopyAndInsertRow()
    Dim iRow As Integer
    
    'note the current row
    iRow = Selection.Row
    'Insert a new row
    Selection.EntireRow.Select
    Selection.Insert Shift:=xlDown
    
    'Select the original row and copy it with formulae
    Rows(iRow + 1).Select
    Selection.Copy
    
    'Paste it into the new row
    Rows(iRow).Select
    ActiveSheet.Paste
    
End Sub

Re: parameter queries

Is this in a user form?

Re: Inserting a line feed during a replace

That's right, but I want the replacement text to be a new line.

I have tried "^0013" (the line feed control charachter) as the replacement text, but with the same result.

Hi All,

I've been trying to replace some marker text "*LINEFEEDGOESHERE*" with an actual line feed.

To do this I am attempting a replaceall, replacing the text with the control characher required. However, when I run it, the control charachter seems to be there, but it isn't doing what it's supposed to. ie, there is a little box there, rather than moving to the next line. Any ideas?

'Get rid of all the linefeed words
        With Selection.Find
            .Text = " *LINEFEEDGOESHERE* "
            .Replacement.Text = vbNewLine
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = True
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With


        Selection.Find.Execute Replace:=wdReplaceAll

Cheers,

Jim

Re: loop ranges and sheets

try this

Sub CopySheets()


    Dim ws As Worksheet
    Dim iCnt As Integer
    Dim x As Integer
    Dim wsCollate As Worksheet
    
'Set the worksheet you want everything copied to
    Set wsCollate = Worksheets("Sheet1")
'And the first column you want to be copied into
    x = 3
    
    'Run through each worksheet
    For Each ws In Worksheets
        'Skip the sheet you want to collate to
        If ws.Name = wsCollate.Name Then GoTo NextSheet


       'Name the top of the column        
        wsCollate.Cells(7, x).Value = ws.Name


        'Run through the necessary rows
        For iCnt = 3 To 22
            wsCollate.Cells(iCnt + 5, x).Value = ws.Cells(iCnt, 2).Value
        Next
        'Increment the column counter
        x = x + 1
NextSheet:
    Next


    Set wsCollate = Nothing


End Sub

Re: File unstable if it gets to big.

I had a similar problem - I got handed 2 150 MB excel sheets to do some data processing, and wierd things started to happen.

I solved it by putting all of the excel worksheet functions into VBA - this not only reduces file size, but you can actually assign the order in which cells are calculated, rather than letting excel iterate over all of your ranges however many times it takes.

Cheers,

Jim

Re: Can't use string in Range variable?

Result = Worksheets("Sheet1").Range(column & exchangerow)

Cheers,

Jim

Re: Print Macro printing wrong sheet

Sorry, didn't read all of your post about adding/removing sheets.

If you want to pass over your sheet to the print function you can reference the codename...

Say You have a sheet with codename Sheet1, Name MySheet.

To get the name variable from the codename you can use

strSheetName= Sheet1.name
'strSheetName now holds the string 'MyName'

If you want to pass the sheet directly you can use

Sub test()
    TestPrint Sheet1
End Sub


Sub TestPrint(sht As Worksheet)


    Debug.Print sht.Name
    
End Sub

Is this what you meant?

Cheers,

Jim