I'll bet that ...xlBook.SaveAs _ "G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant.xlsm" xlBook.Close ...made all the difference in the world!Ya gotta reference the correct object or things can get really weird.Thanks for posting the final version for the archives. It may justRead more
I’ll bet that …
xlBook.SaveAs _
"G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant.xlsm"
xlBook.Close
…made all the difference in the world!
Ya gotta reference the correct object or things can get really weird.
Thanks for posting the final version for the archives. It may just help someone else in the future.
There is no VLOOKUP syntax that is going to be able to find a non-matching text value such as you described.I suggest that you check out this page and see if you can add a column in which you have "rearranged" the names such that they match on both sheets.http://www.cpearson.com/excel/First...BasedRead more
There is no VLOOKUP syntax that is going to be able to find a non-matching text value such as you described.
I suggest that you check out this page and see if you can add a column in which you have “rearranged” the names such that they match on both sheets.
Based on my personal experiences, you should be able to “fix” the majority of your names and with a little bit of manual work, end up with exact matches across both sheets.
Posting Tip: Before posting Data or VBA Code, read this How-To.
Recapping, I figured out what was wrong! After successfully drive mapping the "server" hard drive (i.e. the data storage location) so that the client computer "sees" the data location on the server I made a fatal mistake and received this error: "Another user is currently adding this company to PeacRead more
Recapping, I figured out what was wrong! After successfully drive mapping the “server” hard drive (i.e. the data storage location) so that the client computer “sees” the data location on the server I made a fatal mistake and received this error:
“Another user is currently adding this company to Peachtree Classic Accounting. You cannot access this company until the user has finished.”
Three menus down into the DOS program for Peachtree Classic Accounting is where you actually have to redirect the data search to save / retrieve the data. This was a very dumb mistake I made. I still had the data saving and retrieving to / from the client hard drive. Once I redirected the data file(s) location to P: PCA/XXXXX (from within the program on the client) the data was able to be both retrieved and saved.
First, let's start with a tip about posting data and code in this forum. This tip can be used for both the data table in your first post and formatted code (indents, etc). To line up data in your post, please use the pre tags found above the Reply box. 1 - Click the pre icon found above the Reply boRead more
First, let’s start with a tip about posting data and code in this forum. This tip can be used for both the data table in your first post and formatted code (indents, etc).
To line up data in your post, please use the pre tags found above the Reply box.
1 – Click the pre icon found above the Reply box.
2 – Enter your data between the tags.
3 – Click Preview Follow Up to see if you like the way it looks.
4 – If you need to fix the layout, fix it in the Message box below the Preview box.
5 – Click the “Check To Show Confirmation Page Again” box.
6 – Click either Confirm button to Preview the post again.
Repeat steps 4 – 6 as often as necessary until you like the way the post looks and then click Confirm.
As an example, the data table in your OP could be made to look like this when used with the pre tags:
A B C D
9 8/24/2010 p 15243
10 8/24/2010 p 1500
11 8/29/2010 n 45
12 8/29/2010 p 7410
13 8/29/2010 p 621
14 8/29/2010 n 275
15 9/2/2010 p 15243
16 9/2/2010 p 1500
Now let’s mention a very basic coding practice that you should be aware of:
Rarely do you have to Select a object with VBA to perform an action on it. You can refer to the object directly. For example,
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
can be written as:
ActiveCell.EntireRow.Insert Shift:=xlDown
In fact, you don’t even have refer to the active cell, you can refer to any cell directly:
Range(“B9”).EntireRow.Insert Shift:=xlDown
Or, if using a variable (as we will below):
myRow = 9
Range(“B” & myRow).EntireRow.Insert Shift:=xlDown
Selecting objects makes for bulky and inefficient code.
That said, when I review your code, I don’t see any code where you are checking for a change in the date. If the change in date is the trigger to insert a row, then obviously you need to check for that condition.
The easiest way to do that is to loop through the range and compare each cell to the one below it. If they are not equal to each other, then insert a row between the change.
Now since we are going to use a For-Next loop, we need to know where to start, and where to stop. Based on your example code, I’m assuming that the dates start in B9, so we can start the loop there. To determine the end of the loop, we’ll let VBA determine that for us.
Finally, since we are going to insert rows, which would mess up the For-Next loop, we need to start from the bottom. If we start from the top, insert a row and then let the For-Next counter increment, it will be looking at the new row, which won’t match the row below it and the code would just keep inserting rows at the same spot.
However, if we start at the bottom and count backwards, then the counter will look at a row, insert one below it if required and then move up, thereby always checking cells with a date in them.
So here we go:
Option Explicit
Sub InsertAtDateChange()
Dim lastRow, chkRw As Integer
‘Determine last row with data in Column B
lastRow = Range(“B” & Rows.Count).End(xlUp).Row
‘Loop from bottom of list to Row 9, in reverse order
For chkRw = lastRow To 9 Step -1
‘Compare the current cell to the one below it
‘If they don’t match, insert a row row below the current Row
If Range(“B” & chkRw) Range(“B” & chkRw + 1) Then
Range(“B” & chkRw + 1).EntireRow.Insert shift:=xlDown
End If
‘Decrement the counter and do it again
Next
End Sub
If you highlight the text in WordPad then use Copy (or Ctrl+C keys) you should then be able to open a new (blank) Libra Office document, click the page and then use Paste (or Ctrl+V) keys to get the text in place. I've never known a word processor that cannot have text pasted into it from elsewhere.
If you highlight the text in WordPad then use Copy (or Ctrl+C keys) you should then be able to open a new (blank) Libra Office document, click the page and then use Paste (or Ctrl+V) keys to get the text in place.
I’ve never known a word processor that cannot have text pasted into it from elsewhere.
Close Excel. Open Excel In the new workbook, this time select cells C1 to D14 Right click and format as General Enter new whole numbers only - 1, 2, 3 etc. Select the cells again Format as number with 2 decimal places. Create a scatter chart from cells C1 to D14 Enter your numbers as a formula =1.5/Read more
Close Excel.
Open Excel
In the new workbook, this time select cells C1 to D14
Right click and format as General
Enter new whole numbers only – 1, 2, 3 etc.
Select the cells again
Format as number with 2 decimal places.
Create a scatter chart from cells C1 to D14
Enter your numbers as a formula =1.5/1 instead of 1.5
Do not copy and paste – these numbers must be entered from the keyboard.
What is your default date format in windows.
In control panel look in regional and international settings and post short and long-date formats.
Using a "Helper" column you could associate your states to numbers something like: A B 1) STATE Division 1) WA 1 2) OR 1 3) CA 1 4) AZ 1 5) NY 4 6) RI 4 7) TX 3 1= West Coast 2= Mountain 3= Central 4= East Coast OR something like: A B 1) STATE Division 1) WA West 2) OR West 3) CA West 4) AZ West 5)Read more
Using a “Helper” column you could associate your states to numbers something like:
A B
1) STATE Division
1) WA 1
2) OR 1
3) CA 1
4) AZ 1
5) NY 4
6) RI 4
7) TX 3
1= West Coast
2= Mountain
3= Central
4= East Coast
OR something like:
A B
1) STATE Division
1) WA West
2) OR West
3) CA West
4) AZ West
5) NY East
6) RI East
7) TX Central
How you divide them, is up to you.
Then using a =VLOOKUP() function, just grab the info from column B
It depends.The main function you need is the MID function which has this syntax:MID(text, start_num, num_chars)If every cell has the same numbers of characters before the string you want to extract (in this case 58) and the string is always the same number of characters (in this case 9), then a basiRead more
It depends.
The main function you need is the MID function which has this syntax:
MID(text, start_num, num_chars)
If every cell has the same numbers of characters before the string you want to extract (in this case 58) and the string is always the same number of characters (in this case 9), then a basic MID function can be used:
=MID(A1,58,9)
If some cells might have a different number of characters before the string you want to extract but the string is always the same number of characters (in this case 9), then a FIND function can be added to find the “equal sign” and use that as the start_num argument for the MID function:
=MID(A1,FIND(“=”,A1)+1,9)
If some cells might have a different number of characters before the string you want to extract and the string might have a varying numbers of characters also, then a couple of more FIND functions can be added to determine the number of characters between the “equal sign” and the “ampersand” and use that as num_chars argument for the MID function:
If there is nothing consistent for the formula to find, such as an equal sign before the string and an ampersand afterwards, then things get considerably more difficult and we’ll need some more examples.
Hi, If your requirement is to have all results in one cell, could you instead have what looks like one cell, but is actually several cells. Have scores and + signs in a series of cells. Resize the columns making alternate columns just wide enough for the + signs and the columns with numbers, just wiRead more
Hi,
If your requirement is to have all results in one cell, could you instead have what looks like one cell, but is actually several cells.
Have scores and + signs in a series of cells.
Resize the columns making alternate columns just wide enough for the + signs and the columns with numbers, just wide enough for the largest number.
Highlight all the cells and using Format Cells… Borders, Replace the vertical lines between cells with white lines so that they are invisible.
Retain the horizontal lines and an outside border.
The SUM function in the next column will work as normal, and the scores will appear to be in single cells.
To enter + in a cell on its own precede it by a single quote ‘
You will be able to use the numbers for other functions such as averages, maximums etc.
Have you tried unplugging your router for a minute and while it is rebooting, restart your computer or device? Sometimes this is all that is needed to get a poorly working Wifi to work properly again.
Have you tried unplugging your router for a minute and while it is rebooting, restart your computer or device? Sometimes this is all that is needed to get a poorly working Wifi to work properly again.
VBS Script Open File, Run Macro But Does Not Save The File
I'll bet that ...xlBook.SaveAs _ "G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant.xlsm" xlBook.Close ...made all the difference in the world!Ya gotta reference the correct object or things can get really weird.Thanks for posting the final version for the archives. It may justRead more
…made all the difference in the world!
Ya gotta reference the correct object or things can get really weird.
Thanks for posting the final version for the archives. It may just help someone else in the future.
Click Here Before Posting Data or VBA Code —> How To Post Data or Code.
V Look Up For Jumbled Words In Excel
There is no VLOOKUP syntax that is going to be able to find a non-matching text value such as you described.I suggest that you check out this page and see if you can add a column in which you have "rearranged" the names such that they match on both sheets.http://www.cpearson.com/excel/First...BasedRead more
I suggest that you check out this page and see if you can add a column in which you have “rearranged” the names such that they match on both sheets.
http://www.cpearson.com/excel/First…
Based on my personal experiences, you should be able to “fix” the majority of your names and with a little bit of manual work, end up with exact matches across both sheets.
Posting Tip: Before posting Data or VBA Code, read this How-To.
Network Mapping & Peachtree Classic Accounting
Recapping, I figured out what was wrong! After successfully drive mapping the "server" hard drive (i.e. the data storage location) so that the client computer "sees" the data location on the server I made a fatal mistake and received this error: "Another user is currently adding this company to PeacRead more
Recapping, I figured out what was wrong! After successfully drive mapping the “server” hard drive (i.e. the data storage location) so that the client computer “sees” the data location on the server I made a fatal mistake and received this error:
“Another user is currently adding this company to Peachtree Classic Accounting. You cannot access this company until the user has finished.”
Three menus down into the DOS program for Peachtree Classic Accounting is where you actually have to redirect the data search to save / retrieve the data. This was a very dumb mistake I made. I still had the data saving and retrieving to / from the client hard drive. Once I redirected the data file(s) location to P: PCA/XXXXX (from within the program on the client) the data was able to be both retrieved and saved.
See lessInsert Rows Based On Date In Cells Vba
First, let's start with a tip about posting data and code in this forum. This tip can be used for both the data table in your first post and formatted code (indents, etc). To line up data in your post, please use the pre tags found above the Reply box. 1 - Click the pre icon found above the Reply boRead more
First, let’s start with a tip about posting data and code in this forum. This tip can be used for both the data table in your first post and formatted code (indents, etc).
To line up data in your post, please use the pre tags found above the Reply box.
1 – Click the pre icon found above the Reply box.
2 – Enter your data between the tags.
3 – Click Preview Follow Up to see if you like the way it looks.
4 – If you need to fix the layout, fix it in the Message box below the Preview box.
5 – Click the “Check To Show Confirmation Page Again” box.
6 – Click either Confirm button to Preview the post again.
Repeat steps 4 – 6 as often as necessary until you like the way the post looks and then click Confirm.
As an example, the data table in your OP could be made to look like this when used with the pre tags:
A B C D
9 8/24/2010 p 15243
10 8/24/2010 p 1500
11 8/29/2010 n 45
12 8/29/2010 p 7410
13 8/29/2010 p 621
14 8/29/2010 n 275
15 9/2/2010 p 15243
16 9/2/2010 p 1500
Now let’s mention a very basic coding practice that you should be aware of:
Rarely do you have to Select a object with VBA to perform an action on it. You can refer to the object directly. For example,
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
can be written as:
ActiveCell.EntireRow.Insert Shift:=xlDown
In fact, you don’t even have refer to the active cell, you can refer to any cell directly:
Range(“B9”).EntireRow.Insert Shift:=xlDown
Or, if using a variable (as we will below):
myRow = 9
Range(“B” & myRow).EntireRow.Insert Shift:=xlDown
Selecting objects makes for bulky and inefficient code.
That said, when I review your code, I don’t see any code where you are checking for a change in the date. If the change in date is the trigger to insert a row, then obviously you need to check for that condition.
The easiest way to do that is to loop through the range and compare each cell to the one below it. If they are not equal to each other, then insert a row between the change.
Now since we are going to use a For-Next loop, we need to know where to start, and where to stop. Based on your example code, I’m assuming that the dates start in B9, so we can start the loop there. To determine the end of the loop, we’ll let VBA determine that for us.
Finally, since we are going to insert rows, which would mess up the For-Next loop, we need to start from the bottom. If we start from the top, insert a row and then let the For-Next counter increment, it will be looking at the new row, which won’t match the row below it and the code would just keep inserting rows at the same spot.
However, if we start at the bottom and count backwards, then the counter will look at a row, insert one below it if required and then move up, thereby always checking cells with a date in them.
So here we go:
Option Explicit
See lessSub InsertAtDateChange()
Dim lastRow, chkRw As Integer
‘Determine last row with data in Column B
lastRow = Range(“B” & Rows.Count).End(xlUp).Row
‘Loop from bottom of list to Row 9, in reverse order
For chkRw = lastRow To 9 Step -1
‘Compare the current cell to the one below it
‘If they don’t match, insert a row row below the current Row
If Range(“B” & chkRw) Range(“B” & chkRw + 1) Then
Range(“B” & chkRw + 1).EntireRow.Insert shift:=xlDown
End If
‘Decrement the counter and do it again
Next
End Sub
I Can’t Open Odt Document From An Email Attachment
If you highlight the text in WordPad then use Copy (or Ctrl+C keys) you should then be able to open a new (blank) Libra Office document, click the page and then use Paste (or Ctrl+V) keys to get the text in place. I've never known a word processor that cannot have text pasted into it from elsewhere.
If you highlight the text in WordPad then use Copy (or Ctrl+C keys) you should then be able to open a new (blank) Libra Office document, click the page and then use Paste (or Ctrl+V) keys to get the text in place.
See lessI’ve never known a word processor that cannot have text pasted into it from elsewhere.
Excel Scatter Chart Problems
Close Excel. Open Excel In the new workbook, this time select cells C1 to D14 Right click and format as General Enter new whole numbers only - 1, 2, 3 etc. Select the cells again Format as number with 2 decimal places. Create a scatter chart from cells C1 to D14 Enter your numbers as a formula =1.5/Read more
Close Excel.
Open Excel
In the new workbook, this time select cells C1 to D14
Right click and format as General
Enter new whole numbers only – 1, 2, 3 etc.
Select the cells again
Format as number with 2 decimal places.
Create a scatter chart from cells C1 to D14
Enter your numbers as a formula =1.5/1 instead of 1.5
Do not copy and paste – these numbers must be entered from the keyboard.
What is your default date format in windows.
See lessIn control panel look in regional and international settings and post short and long-date formats.
Excel Formula Help – Match States To Regions
Using a "Helper" column you could associate your states to numbers something like: A B 1) STATE Division 1) WA 1 2) OR 1 3) CA 1 4) AZ 1 5) NY 4 6) RI 4 7) TX 3 1= West Coast 2= Mountain 3= Central 4= East Coast OR something like: A B 1) STATE Division 1) WA West 2) OR West 3) CA West 4) AZ West 5)Read more
Using a “Helper” column you could associate your states to numbers something like:
A B
1) STATE Division
1) WA 1
2) OR 1
3) CA 1
4) AZ 1
5) NY 4
6) RI 4
7) TX 3
1= West Coast
2= Mountain
3= Central
4= East Coast
OR something like:
A B
1) STATE Division
1) WA West
2) OR West
3) CA West
4) AZ West
5) NY East
6) RI East
7) TX Central
How you divide them, is up to you.
Then using a =VLOOKUP() function, just grab the info from column B
See lessExcel – Copying Part Of A Cell To A New Cell
It depends.The main function you need is the MID function which has this syntax:MID(text, start_num, num_chars)If every cell has the same numbers of characters before the string you want to extract (in this case 58) and the string is always the same number of characters (in this case 9), then a basiRead more
The main function you need is the MID function which has this syntax:
MID(text, start_num, num_chars)
If every cell has the same numbers of characters before the string you want to extract (in this case 58) and the string is always the same number of characters (in this case 9), then a basic MID function can be used:
=MID(A1,58,9)
If some cells might have a different number of characters before the string you want to extract but the string is always the same number of characters (in this case 9), then a FIND function can be added to find the “equal sign” and use that as the start_num argument for the MID function:
=MID(A1,FIND(“=”,A1)+1,9)
If some cells might have a different number of characters before the string you want to extract and the string might have a varying numbers of characters also, then a couple of more FIND functions can be added to determine the number of characters between the “equal sign” and the “ampersand” and use that as num_chars argument for the MID function:
=MID(A1,FIND(“=”,A1)+1,FIND(“&”,A1)-FIND(“=”,A1)-1)
If there is nothing consistent for the formula to find, such as an equal sign before the string and an ampersand afterwards, then things get considerably more difficult and we’ll need some more examples.
Click Here Before Posting Data or VBA Code —> How To Post Data or Code.
message edited by DerbyDad03
Add Multiple Numbers Within A Cell
Hi, If your requirement is to have all results in one cell, could you instead have what looks like one cell, but is actually several cells. Have scores and + signs in a series of cells. Resize the columns making alternate columns just wide enough for the + signs and the columns with numbers, just wiRead more
Hi,
If your requirement is to have all results in one cell, could you instead have what looks like one cell, but is actually several cells.
Have scores and + signs in a series of cells.
Resize the columns making alternate columns just wide enough for the + signs and the columns with numbers, just wide enough for the largest number.
Highlight all the cells and using Format Cells… Borders, Replace the vertical lines between cells with white lines so that they are invisible.
Retain the horizontal lines and an outside border.
The SUM function in the next column will work as normal, and the scores will appear to be in single cells.
To enter + in a cell on its own precede it by a single quote ‘
You will be able to use the numbers for other functions such as averages, maximums etc.
Regards
See lessTrendnet TEW-639GR Wireless SLOW
Have you tried unplugging your router for a minute and while it is rebooting, restart your computer or device? Sometimes this is all that is needed to get a poorly working Wifi to work properly again.
Have you tried unplugging your router for a minute and while it is rebooting, restart your computer or device? Sometimes this is all that is needed to get a poorly working Wifi to work properly again.
See less