I have an Excel file that stores about 10,000 lines of data from the top. (Title up to the second line) When the value (character) in column K (11th line) is changed, a page break is made at the changed location. I want to create a macro to insert, but if I use the following macro, a page break will be inserted. It is not done or the reason is unknown and it is in trouble. there (1) Please tell me why there are times when line breaks are not inserted in this macro.
There are no
② Please tell me if there is a place where you can improve.
Applicable source code
errors. No line breaks
Supplemental information (FW/tool version etc.)
vba sub Page break () Dim i As Long Dim SaveKey As Variant 'Delete all page breaks Worksheets ("sheet name"). ResetAllPageBreaks 'Set the first and second lines as the title line Application.PrintCommunication = False With Worksheets ("Sheet name"). PageSetup .PrintTitleRows = "$1: $2" .PrintTitleColumns = "" End With i = 3 SaveKey = Cells (i, 11) .Value 'Loop until column K value is blank Do Until Len (Cells (i, 11) .Value) = 0 'Check if the key has changed If SaveKey<>Cells (i, 11) .Value Then 'Insert page break Worksheets ("sheet name"). HPageBreaks.Add Before: = Cells (i, 1) 'Update key SaveKey = Cells (i, 11) .Value End If i = i + 1 Loop 'Show print preview Worksheets ("sheet name"). PrintPreview End sub
This is the environment for Windows7 32bit Excel2010. The sheet name contains the name of the book sheet used for practical use.
Answer # 1
What you want to do is
"If you look at the data in column K in order from the top and the value of the cell you are looking at is different from the value of the cell above it, you want to insert a page break." But it is that?
"I want to create a program that breaks when the value changes"
When you say "When the value changes",
It is interpreted as the time when the value of the target cell has just changed.
And is n’t it a line break?
Excel won't give you the expected answer if you make a mistake in every word.
For humans, I guess what I want to say by analogy with the context, but ^^;
I'm confused because it doesn't work, I don't have time, and I'm eager to write,
You may have used a strange wording, but don't get it wrong,
If you can't explain, you won't be able to write programs.
Now, why is it ①? The question is that you can't understand unless you move the same macro with the same data, but you have to be able to find the mistake yourself.
I think VBE is used as a tool to write VBA, but this tool is
In order to find mistakes, the program can be run line by line.
If you do this, you may find mistakes.
(It is unknown whether it can be found in this case)
In this case,
"No page breaks inserted or not"
If SaveKey<>Cells (i, 11) .Value Then
So, while stepping, the value being compared is
I want to check what the values are and what I want to do,
I think you should check what happens.
In particular, the difference between half-width and full-width alphanumeric characters cannot be seen on the screen, so
It's a good idea to pay attention to that area.
Other symbolic systems are similar but sometimes different, so
(For example, is it difficult to understand the difference between Excel and Excel?)
Let's watch out.
If you don't understand and want to ask someone else to debug you,
There is one way to check the operation with the same data as well as the macro code.
In this case, data in column K?
I think it would be nice to be calm and confirmed or explained.
Assuming that you debug your code yourself,
Let's organize what we want to do again.
0) Program start
1) Cancel a page break on the sheet
2) Look until data disappears from K3
3) Compare the value of the cell you ’re looking at and the value below (or above)?
Insert page breaks if they are different
4) Return to 2
5) End of program
I think this will happen.
Here, Excel's nephew and the necessary code before writing the code,
Let's explore using a function called "Record Macro".
Sub Macro1 () ' 'Macro1 Macro '' Range ("B6"). Select ActiveWindow.View = xlPageBreakPreview Range ("A1"). Select ActiveCell.FormulaR1C1 = "1" Range ("A1"). Select Selection.AutoFill Destination: = Range ("A1: A37"), Type: = xlFillDefault Range ("A1: A37"). Select Range ("A9"). Select ActiveWindow.SelectedSheets.HPageBreaks.Add Before: = ActiveCell Range ("A21"). Select ActiveWindow.SelectedSheets.HPageBreaks.Add Before: = ActiveCell Range ("A30"). Select ActiveSheet.ResetAllPageBreaks End Sub
Look at what you have done and the recorded code, and also check the help.
As a result, a page break is inserted above the specified cell.
See from K4 and repeat until one cell above is blank
I will write it.
'0) Program start Sub test () Dim i As Long '1) Cancel a page break on a sheet ActiveSheet.ResetAllPageBreaks 'Set initial line number i = 4 '2) Repeat as long as the cell above the target cell is not blank Do While Cells (i-1, "K"). Value<>Empty With Cells (i, "K") '3) If the value of the cell you are looking at is different from the value above it If .Value<>.Offset (-1) .Value Then 'Insert a page break ActiveSheet.HPageBreaks.Add Before: =. Cells End If End With 'Prepare next line number i = i + 1 '4) Return to 2 Loop '5) End of program End Sub
② Please tell me if there is a place where you can improve.
In the first place,
Since there is a function to add a page break to the "subtotal" function,
If you count the number of data with the subtotal function,
Macros are not necessary and can be processed faster than the code I wrote earlier.
If you really do not need a formula,
You can search for the formula that was inserted with the jump function and delete the line.
If there are other formulas,
I think you should search for blanks in the data with the jump function.
If that is n’t enough, search for and replace or filter and find out what you can do.
I think we should solve it.
And if the procedure becomes complicated, then automate the operation (= macro)
I think you should think about it.
If the procedure is clear, try recording it as a macro.
Brush up recorded macros for general use
I think it should be improved.
Answer # 2
Roughly compile the code
Compare the value in column K from the fourth row until the value is empty from the fourth row down, and change if it is different PageThe content is
. I tried moving it with EXCEL at hand, but
Page breaks inserted or not
status could not be confirmed.I think it works correctly according to the code written.
And next, "What you want to do" in the question text
When the value (character) in column K (11th line) is changed, a page break occurs at the changed location
If you want content that can be interpreted as it is read,
The posting code is not fulfilling the request in all.
Is it popular to implement using
Worksheet_Changearoundchanged? I think and
To determine whether the change waschanged, you will need to compare
value before changewith
current value. (The comparison target should not be the value in the upper row.)
Because we are not able to judge what is correct, please check it yourself.
Answer # 3
What the questioner wants to do isIn
"Enter page breaks before and after the character string in column K is switched between the upper and lower cells."
It seems that.
In the explanation of the text, it is misunderstood when you type a character in a cell.
, I don't think there will be any worries when looking at the source.
Conversely, there is a possibility that a page break will occur at an unintended location.
The method of presentation distinguishes between "quote numbers" and "numerical numbers", so page breaks occur where the number 1 and the character "1" are consecutive. I will enter.
If you change all the K columns to the string format, you will tend to think that it is OK. Will occur.
In light of that, I think that the cell contents should be converted to Strings every time.
sub page break () Dim i As Long Dim SaveKey As String 'Delete all page breaks Worksheets ("sheet name"). ResetAllPageBreaks 'Set the first and second lines as the title line Application.PrintCommunication = False With Worksheets ("Sheet name"). PageSetup .PrintTitleRows = "$1: $2" .PrintTitleColumns = "" End With i = 3 SaveKey = CStr (Cells (i, 11) .Value) 'Loop until column K value is blank Do Until Len (CStr (Cells (i, 11) .Value)) = 0 'Check if the key has changed If SaveKey<>CStr (Cells (i, 11) .Value) Then 'Insert page break Worksheets ("sheet name"). HPageBreaks.Add Before: = Cells (i, 1) 'Update key SaveKey = CStr (Cells (i, 11) .Value) End If i = i + 1 Loop 'Show print preview Worksheets ("sheet name"). PrintPreview End sub
SaveKey is changed to String type, and cell data is converted to a string with the CStr function.
, "If no page break is inserted", it cannot be read from the question text.
If you can provide a concrete example of what data will be inserted, page breaks will not be inserted.
Please add a concrete example.
- c ++ - i want to create a program that considers the aspect ratio with a fluoroscopic matrix
- create table with vba
- i want to create an email with multiple files attached using vba
- vba - i want to create a windows dll with rust
- i want to create a vba function in gas and make it work
- python - what do i need to do to create a program for mentioning specific people on time for discord?
- python: i want to create a program that deletes jpg files in all directories and mp4 files of 18 seconds or less all at once wit
- create a program to delete videos under 18 seconds in python at once
- python - create a program to delete only jpegs in multiple folders at once
- vba - create transcription macro from excel file to excel file (conditional)
- i want to create a program in c ++ that extracts lines that have the same character string at the beginning
- java - i want to create a program that adds numbers and displays them in reverse order
- vba - i want to create a member to use as an argument in my own class
- i want to make a program that gets the cell value with excel vba and saves it as a file name
- python - i don't know how to create a program that starts working after being clicked
- vba - error that occurs in a program that displays the book name in a folder using the dir function
- ruby - i want to create a program that generates a two-dimensional array with different numbers of columns for each row
- vba - i'm making a macro to create a pivot table for working time totals an error occurs in the display of soursedata
- vba - i want to automatically create a scatter diagram on a single graph using a macro, but the data range, number of series, et
- record processing in excel vba and a program to paste and register it for each sheet
- python 3x - typeerror: 'method' object is not subscriptable
- python - you may need to restart the kernel to use updated packages error
- xcode - pod install [!] no `podfile 'found in the project directory
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- android studio - emulator: dsound: could not initialize about the error message directsoundcapture
- android studio - unresolved reference comes out in kotlin
- mysql startup failed [error] innodb: the innodb_system data file 'ibdata1' must be writable
- django - oserror: [winerror 123] the file name, directory name, or volume label syntax is incorrect : '<frozen importlib_boot
- python - importerror: cannot import name md5 error cannot be resolved