$$Excel-Macros$$ Re: excel macros -non numeric value

2009-09-02 Thread Chris Spicer
n the Immediate Window by typing: Test Range("A1") Regards, Chris Spicer www.Technicana.com --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tuto

$$Excel-Macros$$ Re: if statements to compare string

2009-08-31 Thread Chris Spicer
ot;A1", "L1"), "Jan") End Sub Regards, Chris Spicer www.Technicana.com --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Vid

$$Excel-Macros$$ TIP: Resize an Array Formula to Its 'Natural' Size

2009-08-13 Thread Chris Spicer
be caused by the presence of another array function within the bounds of the selection" Exit Sub Exit Sub End Sub Regards, Chris Spicer www.Technicana.com --~--~-~--~~~---~--~~

$$Excel-Macros$$ Re: How to Make Excel Recalculate When Cell is Tied to VBA Macro

2009-08-12 Thread Chris Spicer
ur function when the drop-down updates. Try Googling for Excel calculation tree to see how Excel goes about figuring out what to calculate and when. Regards, Chris Spicer www.Technicana.com --~--~-~--~~~---~--~~ -

$$Excel-Macros$$ Re: Code to hide workbook

2009-08-12 Thread Chris Spicer
hough you're normally better to put something in the loop's test to allow you to break out. It's normally a little easier to understand. Regards, Chris Spice

$$Excel-Macros$$ Re: How to Make Excel Recalculate When Cell is Tied to VBA Macro

2009-08-09 Thread Chris Spicer
ggest using the 'Change' event on the worksheet object. I can provide further guidance on this if you like. Regards, Chris Spicer www.Technicana.com --~--~-~--~~~---~--~~ --

$$Excel-Macros$$ Re: Compare 2 Excel files with Addresses

2009-07-29 Thread Chris Spicer
this could be quite an involved process! Regards, Chris Spicer www.Technicana.com --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and

$$Excel-Macros$$ Re: 1004-AutoFill Method of Range Class Failed

2009-07-29 Thread Chris Spicer
Hi LD, Nothing springs to mind. Could you also post the 'SetPasteRangeByColumn' function, plus a sheet containing examples of the data you're working with? Regards, Chris Spicer www

$$Excel-Macros$$ Re: Useful VBA for Resizing Array Formulas

2009-07-28 Thread Chris Spicer
nformation on licences and credits in place. Regards, Chris Spicer www.Technicana.com --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Te

$$Excel-Macros$$ Useful VBA for Resizing Array Formulas

2009-07-27 Thread Chris Spicer
dd-in called TechnicanaUtilities and will upload this to the files section of this site. If you load the add- in, it will automatically run the code each time you press CTRL+SHIFT +A together. Regards, Chris Spicer www.Technicana.com ' Distributed under the Creative Commons licen

$$Excel-Macros$$ VBA Error Handling Video

2009-06-15 Thread Chris Spicer
Hi All, I recently had to give some guidance to some other developers regarding error handling in VBA. I have put this guidance into a short video which I thought might be helpful to developers on this group. http://technicana.com/vbaSection/Videos/ErrorHandling.html Regards, Chris Spicer

$$Excel-Macros$$ Re: Unhide doesn't work in a Worksheet_change event & C++ add-in development

2009-06-12 Thread Chris Spicer
This would confirm whether the event is being fired or not. I know a little bit about C++ add-in development, but this is probably not the best forum for those questions. You can forward the question on to me directly or there may be an XLL forum on Google Groups. Regards, Chris Spicer www.Technica

$$Excel-Macros$$ Re: Merging 100s of Excel Files (Find out the Mistake)

2009-06-12 Thread Chris Spicer
Hi Satish, I had the chance to look at this further this morning. The following code seems to do the trick of pasting one workbook's data under another. All the best, Chris www.Technicana.com Sub CombineFiles() Application.DisplayAlerts = False Application.ScreenUpdating = False vMaster = Ac

$$Excel-Macros$$ Re: Merging 100s of Excel Files (Find out the Mistake)

2009-06-11 Thread Chris Spicer
e following: Range("A1:Z92").Copy As a quick fix, replace your line of code with the following: Range("A1:Z" & Cells(Rows.Count, "B").End(xlUp).Row).Copy A more elegant option might be to use 'Sheet.

$$Excel-Macros$$ Re: Merging 100s of Excel Files (Find out the Mistake)

2009-06-10 Thread Chris Spicer
aste Range("AA" & vStart & ":" & "AA" & Cells(Rows.Count, "A").End(xlUp).Row) Let us know if you have a breakthrough or can provide an example sheet for us to test with. Regards, Chris Spicer http://technicana.com/EasyDeeby.html --~--~--

$$Excel-Macros$$ Re: remove module

2009-06-05 Thread Chris Spicer
ut for is a dangling reference to the xla addin... you should be able to remove that reference programmatically when it comes time to distribute your workbook. I hope this helps, Chris Spicer www.Technicana.com On Jun 4, 8:08 pm, mikeallen7 wrote: > I have 2 vba modules that are both within

$$Excel-Macros$$ Re: remove module

2009-06-05 Thread Chris Spicer
I suppose you could add code to module 1 to unprotect the project it's in, then reprotect itself once you've removed module 2. Would this be suitable for your needs? On Jun 4, 8:08 pm, mikeallen7 wrote: > I have 2 vba modules that are both within a protected project.  I wish > to remove one mo

$$Excel-Macros$$ Re: Comma Concatenator Macro

2009-05-27 Thread Chris Spicer
Rick, You weren't far off - you just need to add an iif statement to your concatenation (see below). Regards, Chris Spicer www.Technicana.com P.S. With regards the line that reads vSQL = vCell, are you sure this is what you're trying to achieve? It seems to be that a blank cell in

$$Excel-Macros$$ Re: Settings in Excel - Calculation - Manual/Automatic

2009-02-26 Thread Chris Spicer
Hi Rajesh, If memory serves, if you open a workbook that is set to 'automatic calculation', then open another workbook that is set to 'manual calculation' in the same session of Excel, then both workbooks will be set to manual calculation. I say 'if memory serves' as this doesn't seem to occur i

$$Excel-Macros$$ Re: How to generate codes for expired contracts?

2009-02-24 Thread Chris Spicer
You're very welcome. On Feb 23, 3:36 pm, Art wrote: > Chris, > > Thanks a lot. I would've spent weeks (not to say months) to figure > this out. > > Tks again, > Art. > > On Feb 21, 12:11 pm, Chris Spicer wrote: > > > > > Hi Art, > > &g

$$Excel-Macros$$ Re: Actually, Rename Sheet after making Sheet Visible

2009-02-24 Thread Chris Spicer
; wksPerformance.Range ("B2").Value wksInitial.Visible = True wksInitial.Name = "InitialCostAlt" & wksInitial.Range ("H3").Value Next i I think it will do what you're looking for. Regards, Chris Spicer Starting up at www.Technicana.com !

$$Excel-Macros$$ Re: How to generate codes for expired contracts?

2009-02-21 Thread Chris Spicer
= "M" MonthCodes(7) = "N" MonthCodes(8) = "Q" MonthCodes(9) = "U" MonthCodes(10) = "V" MonthCodes(11) = "X" MonthCodes(12) = "Z" End Sub Regards, Chris Spicer www.Technicana.com --~--~-~--~