Search results

The first postnumber i can find 10,001 so we may actually be another ~10,000 posts away.

I dont think your connection string is correct. DataLocation = ThisWorkbook.Path & DataLocation Should be DataLocation = ThisWorkbook.Path & DBName.mdb I think, I don't use access so much these days.

I would have just put a few lines of code into add the SUM formula programmatically and then you wouldn't need to delete empty rows, you can also delete your select statements as they don't do anything.

I noticed when posting a reply that it was post number 999996 so thought I would commemerate post number 1000000 with this thread. I'm not sure which number the first post was so not sure how many actual posts there have been but it seemed worth remarking upon in here. So happy one millionth...

Corruption is quite common in excel workbooks, especially if they contain a lot of activex controls, but I've come across it even in small workbooks.

Also what is the error you get from the "update" sub?

I wouldn't worry about the values being calculated from formulas when you append to access it will append the values not the formulas.

It's a long time since I did this. I will need to read upon it before I can give you an answer to do it from excel. It would be easier to do this from access than it would be from excel. To do it from access you would create a linked table from the named range in the excel workbook and then it...

To format the cells you could add something like: dim ws as worksheet set ws = targetrange.parent ws.range("B2:B" & ws.rows.count).numberformat = "[Green] ""+""0.0%;[Red]""-""0.0%" Just replace the B with whatever column the data is in.

Is the formatting applied before or after pasting the data? If you currently do it before pasting the data try changing it to after you paste the data. As for you second issue use something like: worksheets("SheetName").Range("Whatever the range is").numberformat = "General/%" Create a...

Nice stuff, hadn't thought of doing it like that but you need to subtract 1 from the char code so it returns the correct number. IF((YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1))<4 ,CHAR(CODE("A")-1+(YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1))) ,"D") I really like...

Your missing the point. The month function returns an integer between 1 and 12 so when the month in January the month function returns 1 and if you subtract one from that the result is 0 not 12. Your second function works but I would still include the condition for "D" which allows the final...

That won't work for some months, such as January.

This isn't the easiest but this should work, the formula assumes the date for comparison is in cell A1: =IF((YEAR(NOW())-YEAR(A1))*12+MONTH(NOW())-MONTH(A1) =...

I just googled "inactivty excel vba" and found a number of solutions.

Private Sub Worksheet_Change(ByVal Target As Range) Call Sort target.select End Sub

Well if you know the why and how the sheet name changes you can sometimes be able to include that logic in your vba to be able to identify the correct sheet. Anyway you have a couple of options. 1. Use the ordinal position of the sheet. This isn't something I am a fan off but if the output is...

A bit more info would help Why does the name change? How is the new name determined? How many sheets are in the workbook?

c.parent.cells(c.row,30).clearcontents

Set up some data validation on the cell to allow decimal and require the value to be less than 1. Entering either "0.5" or "50%" will both work then but "50" won't.