Replies

You could do =left(a1,search(“,”,a1)-1). This assumes there’s only 1 comma in each name. To get the first name, you could do =right(a1,len(a1)-search(“,”,a1))

To make the chart fit to paper, you want the chart to be a sheet as opposed to being an object on a sheet. Just right click on the chart, select Location, and choose “As New Sheet”.

To change the order of the data, just right click on any one of the bars. Click on Format Data Series. One of the tabs you will get at the top is “Series Order”. You can rearrange the sequence of bars there.

Results are attached.

This was done in Excel97.

To make the chart fit to paper, you want the chart to be a sheet as opposed to being an object on a sheet. Just right click on the chart, select Location, and choose “As New Sheet”.

To change the order of the data, just right click on any one of the bars. Click on Format Data Series. One of the tabs you will get at the top is “Series Order”. You can rearrange the sequence of bars there.

Results are attached.

This was done in Excel97.

Not sure what you mean by autofill, but here’s a quick way to get what you need, and it’s easily expandable as you add more rows.

Type 1/31/89 in cell A1. In cell A2, type =A1. Copy this down the column 3 more times. In cell A6, type =eomonth(A1,1). Format all 6 cells as M-YY. Now just drag cell A6 down the column as many times as you need.

Not sure what you mean by autofill, but here’s a quick way to get what you need, and it’s easily expandable as you add more rows.

Type 1/31/89 in cell A1. In cell A2, type =A1. Copy this down the column 3 more times. In cell A6, type =eomonth(A1,1). Format all 6 cells as M-YY. Now just drag cell A6 down the column as many times as you need.

I’m a self-taught VBA guy, but one thing I’ve learned is to never use a cell reference in VBA if at all possible. One of your first lines is range(“af1”).select. If someone inserts a row or column, the cell refs in your VBA will no longer be correct. If possible, it’s better to use range names in your VBA. In your example, it looks like you’re receiving data from someone else and you want to automate adding some formulas to it, so it may not make sense to create a range name.

It looks to me like you’re looking down Column B until you get to CGS. For each row, if column AF has “PST”, then update running total dsub5 with the value in column Y and dsub6 with the value in column AB. I’m not sure this is best solved using a macro. Have you tried using the SUMIF function? Perhaps =SUMIF($AF1:$AF50,”PST”,Y1:Y50) at the bottom of column Y and then copy it over to the bottom of column AB.

You can delete the ELSE line if there’s nothing to do when Activecell.value “PST”

You can simplify the formatting part of your macro a little bit:

With Selection
Interior.ColorIndex = 6
Font.Bold = True
.Borders(xlEdgeTop).LineStyle = xlContinuous
End With

I’m not sure how many rows of data you have to process, but if it’s thousands, you can speed up execution by putting Application.Screenupdating=False at the beginning of your macro and Application.Screenupdating=True at the end. This turns off redrawing the display during execution.

I’m a self-taught VBA guy, but one thing I’ve learned is to never use a cell reference in VBA if at all possible. One of your first lines is range(“af1”).select. If someone inserts a row or column, the cell refs in your VBA will no longer be correct. If possible, it’s better to use range names in your VBA. In your example, it looks like you’re receiving data from someone else and you want to automate adding some formulas to it, so it may not make sense to create a range name.

It looks to me like you’re looking down Column B until you get to CGS. For each row, if column AF has “PST”, then update running total dsub5 with the value in column Y and dsub6 with the value in column AB. I’m not sure this is best solved using a macro. Have you tried using the SUMIF function? Perhaps =SUMIF($AF1:$AF50,”PST”,Y1:Y50) at the bottom of column Y and then copy it over to the bottom of column AB.

You can delete the ELSE line if there’s nothing to do when Activecell.value “PST”

You can simplify the formatting part of your macro a little bit:

With Selection
Interior.ColorIndex = 6
Font.Bold = True
.Borders(xlEdgeTop).LineStyle = xlContinuous
End With

I’m not sure how many rows of data you have to process, but if it’s thousands, you can speed up execution by putting Application.Screenupdating=False at the beginning of your macro and Application.Screenupdating=True at the end. This turns off redrawing the display during execution.

I thought I’d play around with Excel 97’s limits for internal calcs and tried the following:

cell A1: =VALUE(“1234567890123456”)
cell B1: various values
cell C1: =A1+B1

When B1=5, C1=1,234,567,890,123,450
When B1=6, C1=1,234,567,890,123,460
When B1=5.12, C1=1,234,567,890,123,450
When B1=5.13, C1=1,234,567,890,123,460

Can you explain this?

I thought I’d play around with Excel 97’s limits for internal calcs and tried the following:

cell A1: =VALUE(“1234567890123456”)
cell B1: various values
cell C1: =A1+B1

When B1=5, C1=1,234,567,890,123,450
When B1=6, C1=1,234,567,890,123,460
When B1=5.12, C1=1,234,567,890,123,450
When B1=5.13, C1=1,234,567,890,123,460

Can you explain this?

Thanks, Legare. I stand corrected. I guess I’ve never had to deal with numbers over 999 trillion. Hopefully the original poster is dealing with part numbers or something and not the number of parts he needs to produce.

Thanks, Legare. I stand corrected. I guess I’ve never had to deal with numbers over 999 trillion. Hopefully the original poster is dealing with part numbers or something and not the number of parts he needs to produce.

No offense taken, whatsoever.
I wonder if we scared away the original poster??

No offense taken, whatsoever.
I wonder if we scared away the original poster??

I don’t know about Access, but in Excel the number shouldn’t be changed at all. If you make the Excel column wider, I suspect that you will see the correct number. You could also click on a cell and see what number shows up in the formula bar.

I’ve only done a little bit with Access, but I suspect it wouldn’t change the number either.

I don’t know about Access, but in Excel the number shouldn’t be changed at all. If you make the Excel column wider, I suspect that you will see the correct number. You could also click on a cell and see what number shows up in the formula bar.

I’ve only done a little bit with Access, but I suspect it wouldn’t change the number either.