Free Microsoft Excel 2013 Quick Reference

Check value and insert row Results

I have data in a worksheet (say row1, row 2, row 3, etc.), and column D has a number in it. I want to check that number. If it is 0 to 6, do nothing; if it is between 6 & 12, I want to insert a blank row (row #2); if it is between 13 & 18, then add two rows (row2, row3), and so on. What formula should I use? Macro?

I need help creating a macro that will automatically insert a row if the value in cell AA12 is greater than the value in cell AD12. This must be done down the entire worksheet by checking those two columns (AA and AD). I believe I need to include a IF statement in my insert row macro. Please help.

Hello.

I am building a tool in Excel which must copy data from a record in workbook "A" and paste into workbook "B". A primary key identifies each record in workbook "A". The tool must search workbook "B" for the primary key. If the primary key is not found in workbook "B", the tool must paste the record from workbook "A" after the last (bottom) record in workbook "B" (The easy part).

The hard part is: if the primary key IS found in workbook "B", the code must OVERWRITE all of the data on that row with the data from workbook "A". This prevents duplicate entries of the same key.

Example 1: Primary key in workbook "A" = 9999. This value does NOT exist in workbook "B", so the code will copy the entire record from workbook "A" and insert it as a new record after the last existing record in workbook "B".

Example 2: Primary key value 9999 ALREADY EXISTS in workbook "B", so the code must 1) identify the row number which contains the record with that key, and 2) overwrite whatever is in that record with the information from workbook "A".

This is my first post in this forum so please go easy on me.

UPDATE: Here's the code i have so far - it successfully creates a new record at the bottom of workbook "B". I am missing the code to check for the primary key and if found to overwrite.


	VB:
	
 OpenDB() 
     '
     ' OpenDB Macro
     '
    Application.ScreenUpdating = False 
     
     'Copies the selected record from workbook "A"
    Sheets("DB_Entry").Visible = True 
    Sheets("DB_Entry").Select 
    Rows("2:2").Select 
    Selection.Copy 
    Sheets("Input V1").Select 
    Sheets("DB_Entry").Visible = False 
     
     'Opens workbook "B"
    Workbooks.Open "C:Database v1.0.xlsm" 
    Windows("Database v1.0.xlsm").Activate 
     
     'Pastes the record after the last existing record in workbook B
    Sheets("Database v1.0").Select 
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row 
    For x = 2 To FinalRow 
    Next x 
    NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
    Cells(NextRow, 1).Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
     
    ActiveWorkbook.Save 
    ActiveWorkbook.Close 
     
    If MsgBox("Database Successfully Updated!", vbQuestion, "Success") = vbNo Then 
        Exit Sub 
    End If 
     
     '
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to achieve a row insert based on matching criteria. I need to check a column of text values, and each time the text value changes, copy cells (a1:c1) and insert copied selection to the row before text change.

I am only concerned with the text in the first column. For example, I have a column with sometext in each row, when the row changes to somenewtext, I want to copy the header information and insert into row before the text changed.

Header1|Header2|Header3|
sometext
sometext
sometext
somenewtext

Searching the forums, I found conditional page breaks http://www.ozgrid.com/Excel/excel-co...age-breaks.htm and tried to adapt the code, but have been unsuccessful in getting it to work for my needs. I have tried the following, but cant figure out how to insert the rows in the correct place. Here is what I tried.

	VB:
	
 cln() 
    Dim myRange As Range 
    Dim rngCell As Range 
     
    With Worksheets("pendingRpt") 
        Set myRange = .Range(.Range("A1"), .Range("A65536").End(xlUp)) 
        For Each rngCell In myRange 
            If rngCell.Text  rngCell.Offset(1, 0).Text Then 
                rngCell.Select 
                With Selection.Interior 
                    Range("A1:C1").Select 
                    Selection.Copy 
                    Selection.Insert shift:=xlDown 
                End With 
            End If 
        Next 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any and all help is appreciated.

I am attempting to copy and insert the entire row only if the value of that row in column k is greater than zero. If it is i need to have that many rows copied and inserted then that value in k to be 1. then it needs to continue until all rows have been checked and the column K value for each = 1. There are over 15000 lines of data.

I'm newer to using VBA and could really use some help here. I attached a sample of the table and some of the code I started on. I had it working so it would copy the rows then paste the desired amount but could never figure out how to get it to check and repeat for all lines then return value of 1 in column K.
A B C D E F G H I J K L M N O 1 LFW LDA name 10/5/2010 BIC 898 1 48 40 40 1 1.001 16:54:29 1 124961760-1 2 LFW LDA name 10/5/2010 BIC 898 1 48 40 40 1 1.001 16:54:29 1 124961760-2 3 LFW LDA name 10/5/2010 BIC 898 1 48 40 40 3 1.001 16:54:29 1 124961760-3 4 LFW LDA name 10/5/2010 BIC 898 1 48 40 40 5 1.001 16:54:29 1 124961760-4
Sub CopyRowInsert()
If ThisWorkbook.Sheets("DATA").ActiveCell.Value > 1 Then
Do
Rows("2:2").Select
Range("K2").Activate
Selection.Copy
Selection.Insert Shift:=xlUp
Dim x As Integer
x = ActiveCell.Value
x = x - 1
ActiveCell.Value = x
Loop Until x = 1

End If
End Sub

I am trying to achieve a row insert based on matching criteria. I need to check a column of text values, and if the text values are repeated more than x times, I want to insert a row each time the text value repeats more than x times.

So I want to take the following

sometext
sometext
sometext
somenewtext
somenewtext
somenewtext
othertext

And turn it into

sometext
sometext

sometext
somenewtext
somenewtext

somenewtext
othertext

Searching the forums, I found conditional row deletes which delete rows repeated x times, and tried to adapt the code, but have been unsuccessful in getting it to work. I have tried the following, and have had my system become unresponsive.


	VB:
	
 insertFail() 
    Dim r As Long 
     
    For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 
        If Cells(r, 1) = Cells(r - 1, 1) Then 
            r = r + 1 
        Else 
            If r >= 2 Then Rows(r).Insert Shift:=xlDown 
        End If 
    Next r 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any and all help is appreciated.

Good morning,

I have included two workbooks in this post to help illustrate what I am trying to achieve.

Primary workbook (WORKBOOK.xlsx) contains origin point in Column A, Destination point in Column B, and then various types of info in the remaining columns (i.e. C,D, and E)

Secondary workbook (EXTENSIONS.xlsx) is basically a look-up table which tells you if you have value "B" as a destination (or origin) in one of the rows of data in the primary workbook, that the data you have in columns C,D, and E is also the same as if your destination was "G","H","I" etc.

Sheet "ORIGINAL" shows an example of what the starting data would look like. Sheet "DESIRED" shows what I am trying to get to.

Effectively what I want the VBA to do is look up the value in B2 initially (i.e. "B"), and find all the values in column "A" of the EXTENSION workbook that have the same reference value (i.e. "B").

Once those are identified I want it then to create a line under row 2 of the primary workbook for every value that was found in the extension workbook. Once all of the destinations have been "extended", it should then check the origin value to see if there are any values that need to be extended based on it.

So for example, my first row shows origin "A" and destination "B". When the code checks the EXTENSION file it will find that "B" is also equal to "G", "H", and "I". It will then create rows under row 2 which now show the same origin "A", and the same data in columns "C:E", but changes the value of in column B to "G", "H", and "I".

Once that is completed it will also find that origin "A" is also equal to "Q", so all the rows with origin "A" will be duplicated and inserted under the data with the same values in the destination column, as well as the data columns "C:E".

Any "new rows" of data that have been added through this process would need to have the font colour changed to red.

Please take a look at this and let me know if you have any questions. Looking very forward to some help with this.

Thanks guys,

WORKBOOK.xlsx
EXTENSIONS.xlsx

I'm using excel 07, and I need to copy 2 columns from a Dynamic Named Range to new rows in an existing sheet in the same book. I need the inserted rows to copy formulas/format from the rows above, and insert the First and Last names from Sheet1's dynamic named range.

I've been able to somewhat achieve this by vlookup, but I would prefer a macro loop that goes through the dynamic named range and finds new entries and inserts a new row in sheet2.

I have attached the workbook with dummy values. Sheet2 is where all the action happens. To give an idea of the process, a technician exports values from the web into an Excel file. They then "copy" the Last Name & First Name columns from the export file and "paste" them into Sheet1. I need a macro that, when executed, compares Existing Last Name & First Name from Sheet2 to Sheet1 and creates new formatted rows for the new names (the rows will be blank, but will have the same format as the row above).

I've been researching this for days, trying different variations, etc., and have all but given up. I can feel that it is just out of reach, but all of the different function terms are starting to confuse me. I have some experimental sheets in the book, but all I care about are Sheet1 and Sheet2. You can see what Sheet2 should look like, it just needs to be automated.

In the book I attached, there is a new client in Sheet1 that is not in the Sheet2. I have a "manual" insert row macro that keeps the same format, but doesn't compare to the other list to find "new" names.

Also, if Sheet2 could expand and contract as needed by adding rows for new clients on Sheet1, and deleting names no longer on Sheet1, then I wouldn't have to "pre-format" 500 rows. End-user simplicity is preferred, and I will be hiding all sheets other than Sheet1 and Sheet2, so, anything we can do to automate as much as possible is the way to go.

If someone knows how to do it, but is only willing to write this up for a fee, please inbox me with what it would take for you to do the code. I am doing this as part of my internship, so I can't really afford to pay for it. But, if it's the difference between getting the macro and "TLDR", then I'll do what I have to.

Thanks in advance for any advice you can offer. I will check back frequently to see if there are any questions or suggestions.

I am an excel vb novice and I am getting a bit lost, so any help would be appreciated.

I have a total input sheet (A-E Summary Cashbooks Totals LIVE) where transactions are entered. If in column A, "HG6A" or "ALL" is entered, the macro will post these transactions to the "A Cashbook" workbook.(eventually there will be one for B,C,D and E, so if HG6B and ALL are chosen they post to the B Cashbook etc). I have managed to write a macro that seems to do this: copyRows_1() (see below)(although may not be the most efficient). The only problem is that although it posts the data it will keep posting the same data below what has already been posted everytime the macro is run. I only want transactions to be posted to "A Cashbook" if it hasnt been posted before.

To get around this I have given each transaction a unique number (column E) and the idea is that it will check to see if any numbers in this column on (A-E Summary Cashbooks Totals LIVE) have been posted before on the "A Cashbook" sheet, if the number appears on both then it wont post the transaction but if it doesnt appear on "A Cashbook" it will post the new entry from ("A-E Summary Cashbooks Totals LIVE"). I have attempted this in the macro Check() from looking at various threads on the internet but it is not correct. It is trying to compare the two columns by looping through and then calling the copyRows_1() procedure to copy the row if there is a number that is unique on the input sheet.

I have attached the two files (i have commented out the check procedure as it just crashes) and posted the code below, and would appreciate some tips on getting this to work.

Thanks
Peter


	VB:
	
     
    Dim CompareRange As Variant, x As Variant, y As Variant 
     
    Range(Cells(1, 5), Cells(Rows.Count, 5).End(xlUp)).Select 
     
    Set CompareRange = Workbooks("A Cashbook.xls").Worksheets("A Capital GBP").Range("E:E") 
     
     ' Loop through each cell in the selection and compare it to
     ' each cell in CompareRange.
     
    For Each x In Selection 
        For Each y In CompareRange 
            If x  y Then 
                 
                Call copyRows_1 'if cells dont match then copy over the row to the destination
            Else 
                Exit Sub 
            End If 
        Next y 
    Next x 
End Sub 
 
 
Sub copyRows_1() 'Procedure to copy over entry row from total input sheet to individual cashbook if relates to Hg6A or ALL
     
    Dim Fund As Range, Cell As Object 
     
    Set Fund = Workbooks("A-E Summary Cashbook Totals LIVE.xls").Worksheets("TOTAL INPUT INVESTMENT").Range("A11:A28") 
     
    For Each Cell In Fund 
         
        If IsEmpty(Cell) Then 
            Exit Sub 
        End If 
         
         
        If Cell.Value = "HG6A" Or Cell.Value = "ALL" Then 'if HG6A or ALL then copy row to individual sheet
             
            Cell.Columns("A:G").Copy 
             
            Workbooks("A Cashbook.xls").Worksheets("Capital GBP").Activate 
            ActiveSheet.Range("A65536").End(xlUp).Select 
            Selection.Offset(1, 0).Select 
            ActiveSheet.Paste 
             
            ActiveSheet.Range("A65536").End(xlUp).Select 
            Selection.Offset(1, 0).Select 
            ActiveCell.EntireRow.Insert shift:=xlDown 
             
            Dim y As Long 
             
            y = Range("A65536").End(xlUp).Row 'copy down formulas in destination sheet once entry posted
            Range("G247").AutoFill Destination:=Range("G247:G" & y), Type:=xlFillDefault 
            Range("H247").AutoFill Destination:=Range("H247:H" & y), Type:=xlFillDefault 
            Range("I247").AutoFill Destination:=Range("I247:I" & y), Type:=xlFillDefault 
            Range("J247").AutoFill Destination:=Range("J247:J" & y), Type:=xlFillDefault 
            Range("K247").AutoFill Destination:=Range("K247:K" & y), Type:=xlFillDefault 
            Range("L247").AutoFill Destination:=Range("L247:L" & y), Type:=xlFillDefault 
            Range("M247").AutoFill Destination:=Range("M247:M" & y), Type:=xlFillDefault 
            Range("N247").AutoFill Destination:=Range("N247:N" & y), Type:=xlFillDefault 
            Range("O247").AutoFill Destination:=Range("O247:O" & y), Type:=xlFillDefault 
            Range("P247").AutoFill Destination:=Range("P247:P" & y), Type:=xlFillDefault 
            Range("Q247").AutoFill Destination:=Range("Q247:Q" & y), Type:=xlFillDefault 
            Range("R247").AutoFill Destination:=Range("R247:R" & y), Type:=xlFillDefault 
            Range("S247").AutoFill Destination:=Range("S247:S" & y), Type:=xlFillDefault 
            Range("T247").AutoFill Destination:=Range("T247:T" & y), Type:=xlFillDefault 
            Range("u247").AutoFill Destination:=Range("u247:u" & y), Type:=xlFillDefault 
            Range("v247").AutoFill Destination:=Range("v247:v" & y), Type:=xlFillDefault 
            Range("w247").AutoFill Destination:=Range("w247:w" & y), Type:=xlFillDefault 
            Range("X247").AutoFill Destination:=Range("X247:X" & y), Type:=xlFillDefault 
            Range("Y247").AutoFill Destination:=Range("y247:y" & y), Type:=xlFillDefault 
            Range("Z247").AutoFill Destination:=Range("Z247:Z" & y), Type:=xlFillDefault 
             
        End If 
         
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I was wondering if anyone can help me

Basically, I'm trying to figure how how to create a macro that will add missing rows to a series of tables

Each spreadsheet represents a site, and each site has a series of tables (statistical analysis run for every site...same variables). My tables are missing rows where zero values would normally appear. The only difference between the site spreadsheets should be the actual data (rows should all correspond). Right now--because of missing rows--one sheet may only take up 80 rows while another goes all the way to 100.

Can someone help me figure out how to create a macro that will recognize the table by variable (in the title), check the list of rows in the table against an array of possible values for that variable and insert rows with zero values where it finds a missing value label?

I am including a couple screenshots that show a bit of what I am talking about. No calculations need to be done on any of these values, I really just need them to make the rows in all the spreadsheets consistent so that they can be put into reporting software.

I will greatly appreaciate any and ALL help I can get!

Screenshot - Tables with missing values:
Screenshot - Tables with rows inserted:

The question is how would i go about:

Checking the value in a cell on sheet1. If that value is not found
in the same cell on sheet2

then

Copy entirerow on sheet1 and insert into sheet 2.
then loop through to the next cell down.

its a tough one. taking me a while.
any help will be appreciated.

don't seem to get this so i am doing it 1 step at a time.

recorded a macro:

input data string
copy row 4
Insert row above (move row 4 to row 5 and copy/insert 'old row 4' as 'new row 4)

stop recording

added what i thought would work for 'input data string' (just learning by the way)

and came up with this code but it does not seem to do anything

Sub Insertrow()

If Range("E4").Value = True Then

Rows("4:4").Select
Selection.Copy
Rows("4:4").Select
Selection.Insert Shift:=xlDown

End Sub

this is what i want to do:

step 1 input into A4, C4, E4
step 2 check to see if input is in E4
step 3 if blank do nothing
step 4 if value in E4 then insert new row above with coding from old row 4

Is it possible to create a macro to check the value of a specific column and depending on the value, then no row need to be added or one row or two rows need to be inserted as well as the new row data will be filled with some same data and calculated data from the above row.

For example, the worksheet may have about 1000 rows with column A contains person ID, B contains OrderCode, C contains OrderDate, D contains TotalHrs, E contains NorHrs, F contains OTHrs, G contains PreHrs. If TotalHrs has value of 11.5 then on the current row the NorHrs will have the value of 9, then a new row will be inserted below which has the same value of the above row from column A to C while the TotalHrs and OTHrs will have value of 1, finally a second row will also be inserted similar as above but TotalHrs and PreHrs will have value of 1.5.

The main idea is if in the checking row, if TotalHrs is less than or equal to 9, no new row need to be inserted, if TotalHrs is greater than 9 but less than 10 then only one row needs to be inserted, and if TotalHrs is greater than 10 then 2 rows need to be inserted.

I am posting this same question that I put on another forum but haven't received an answer from the other yet. Here is the link for reference http://www.ozgrid.com/forum/showthre...295#post575295

I am attempting to copy and insert the entire row only if the value of that row in column k is greater than zero. If it is i need to have that many rows copied and inserted then that value in k to be 1. then it needs to continue until all rows have been checked and the column K value for each = 1. There are over 15000 lines of data.

I'm newer to using VBA and could really use some help here. I attached a sample of the table and some of the code I started on. I had it working so it would copy the rows then paste the desired amount but could never figure out how to get it to check and repeat for all lines then return value of 1 in column K.

A B C D E F G H I J K L M N O
1 LFW LDA name 10/5/2010 BIC 898 1 48 40 40 1 1.001 16:54:29 1 124961760-1
2 LFW LDA name 10/5/2010 BIC 898 1 48 40 40 1 1.001 16:54:29 1 124961760-2
3 LFW LDA name 10/5/2010 BIC 898 1 48 40 40 3 1.001 16:54:29 1 124961760-3
4 LFW LDA name 10/5/2010 BIC 898 1 48 40 40 5 1.001 16:54:29 1 124961760-4

Sub CopyRowInsert()
If ThisWorkbook.Sheets("DATA").ActiveCell.Value > 1 Then
Do
Rows("2:2").Select
Range("K2").Activate
Selection.Copy
Selection.Insert Shift:=xlUp
Dim x As Integer
x = ActiveCell.Value
x = x - 1
ActiveCell.Value = x
Loop Until x = 1

End If
End Sub

Hi , I m very novice to macros , I need a very needed help to construct a macro , actually i have a set of rows which contains few columns that have different numbers , I want to have a macro that would compare or check all the columns contain numbers and select the highest maximum value from the row and then it will compare the selected value with some number i.e if the selected highest number is 3 then it will check if 3 not greater than 5 , then it will simply put 5 in a blank cell within that row. And the macro would repeat the same for each row till the last.

I m trying to give you a visualization of the actual scenario:

Col A Col B Col C New Column
row 1: 4 1 7 if Col C(Max value) <=8 8 will be insert in this col
row 2: 5 8 10
row 3: 98 49 56

now the macro would start from row 1 compare Col A ,Col B, Col C and get the Highest value (e.g 7 for row 1) then it will check if value of Col C <= 8(this value will be in the macro itself) then it will simply put 8 in the Column new.The same will be repeated till the last row .

It will be highly appreciated if anyone can give me a macro doing the things i have mentioned for me. Thanx in advance

Hi there -
thanks again for helping me out so far.
I need to extend my existing macro in the following way:

I have x nr of worksheets where my values of interest are in column B9:B15.
In column A9:A15 I have the associated codes for B9:B15, namely numbers from 1 (for A9) through 7 (A15). Problem is that for some worksheets one or more cells (and thus rows) are missing, so the range of B9:B15 is actually the wrong range. What I need to do is to make sure that every worksheet has (a)
values 1-7 in column A9:A15 (and thus automatically corresponding values of interest in column B9:B15).
The macro should thus loop through all the worksheets and check whether A9=1 and A10=2 and A11=3 etc. If not, (a) a row should be inserted and the cell in column A should be labeled with the missing value and (b) a zero should be automatically inserted in the corresponding cell in column B.
So let's say that A11 is equal to 4 instead of 3 (because it was skipped), a row should be inserted and A11 changed to 3, while the next row (A12) will be equal to 4. At the same time, B11 would be equal to 0 (zero).
Any help is appreciated!!!

I'm trying to create a command button that when pressed will copy a particular sheet from another excel file and insert in a certain position.

This position is determined by a counter kept on a sheet within the workbook called "Counter". On this worksheet it has two variable, one counts the number of estimates, the other counts the number of Invoices.

I'm doing this so that When I press the newEstimate button it can copy the page from the template and insert it into the position 1 + the counter for estimates.

After this the counter is of course incremented.

At the moment I have only implement the case when the file exists and is not yet open:

Private Sub
EstimateButton()
Dim newText As String
Dim shpCaller As Shape

Set shpCaller = ActiveSheet.Shapes(Application.Caller)

If Range("N" & shpCaller.TopLeftCell.Row).Value <> "" Then
    newText = "Job " & Range("N" & shpCaller.TopLeftCell.Row).Value
    Dim checkFilename As String
    Dim check As String
    Dim SrcBook As Workbook
    Set SrcBook = ThisWorkbook
    checkFilename = SrcBook.Path & "" & newText & ".xlsm"
    If Dir(checkFilename) <> "" Then
        If Not IsFileOpen(checkFilename) Then
            Workbooks.Open (checkFilename)
            
            Dim DstBook As Workbook
            Set DstBook = Workbooks.Open(SrcBook.Path & "" & newText & ".xlsm")
            
            Dim TempBook As Workbook
            Set TempBook = Workbooks.Open(SrcBook.Path & "Job Template.xlsm")
            
            Dim EstimateName As String
            EstimateName = "Estimate" & DstBook.Worksheets("Counter").Range("B1").Value +
1
            Dim Number As String
            Number = "Estimate" & DstBook.Worksheets("Counter").Range("B1").Value
            TempBook.Worksheets("Job Estimate1").Copy after:=DstBook.Worksheets(Number)
            ActiveSheet.Name = EstimateName
            DstBook.Worksheets("Counter").Range("B1").Value =
DstBook.Worksheets("Counter").Range("B1").Value + 1
        Else
            Workbooks(newText & ".xlsm").Activate
        End If
    Else
    Dim NewBook As Workbook
    Set NewBook = Workbooks.Open(SrcBook.Path & "Job Template.xlsm")
    ' Job Number
    NewBook.Worksheets(2).Range("I16").Value = SrcBook.Worksheets(1).Range("N" &
shpCaller.TopLeftCell.Row).Value
    ' Their name
    NewBook.Worksheets(2).Range("B15").Value = SrcBook.Worksheets(1).Range("D" &
shpCaller.TopLeftCell.Row).Value
    ' First line of address
    NewBook.Worksheets(2).Range("B16").Value = SrcBook.Worksheets(1).Range("F" &
shpCaller.TopLeftCell.Row).Value
    ' Second
    NewBook.Worksheets(2).Range("B17").Value = SrcBook.Worksheets(1).Range("G" &
shpCaller.TopLeftCell.Row).Value
    ' Post Code
    NewBook.Worksheets(2).Range("B18").Value = SrcBook.Worksheets(1).Range("H" &
shpCaller.TopLeftCell.Row).Value
        With NewBook
            .Title = newText
            .Subject = newText
            .SaveAs FileName:=SrcBook.Path & "" & newText
        End With
    End If
Else
ErrMsg:
MsgBox ("Job Should always have a number."), , "NO JOB NUMBER"

End If
End Sub
For some reason it is telling me that the file is already open, then giving a Subscript out of range error on the line:

Any help would be much appreciated, thank you!

In Excel, I need to check for percents >0 and <100 in multiple columns, insert a new row with copied data from that row, and modify both rows. If this occurs in--for example--Row 27, I need to copy Row 27 and insert the copied row before Row 28, while changing the percents in both rows and also an amount from a different column. The percent data is contained in 10 consecutive columns (X through AG), and the amount data is in column AH.
----
Example:
Row 27: Column X (40%), Column Y (0%), Column Z (0%) … Column AD (60%) ... Column AH ($10,000)
--
This needs to become as follows:
Row 27: Column X (100%) ... Column AD (0%) ... Column AH ($4,000*)
Row 28**: Column X (0%) ... Column AD (100%) ... Column AH ($6,000*)

*Calculated by multiplying the original % by the original amount (e.g. 40% * $10k = $4k)
**Inserted by the macro
--
I want everything in Row 28 to be identical to that in Row 27 (values, formulas, etc)--EXCEPT for the modified columns (percents/amounts).
Maybe another way to do this would be to add multiple new rows (one for each % > 0 and < 100)--each with their respective modified data--and then delete the original row from which they were copied. I know almost nothing about writing macros, but I can guess that it might be easier if the source row were unchanged during the iterations.
--
ALSO: I need this to work for rows with multiple % splits (e.g. Row 27: Column X (10%), Column Y (10%) … Column AG (10%) … [etc.]); I need to insert enough rows to eliminate all % splits (resulting in columns X through AG only containing “0%” or “100%” values). The macro must be able to detect the number of % splits and the % in each column in order to determine the number of rows to insert, and to calculate the value to be placed in the amount column of each new row.
I don’t particular care if this requires multiple iterations, though it would be nice if it could be done in one step. It would also be great if this could be applied to an entire spreadsheet without much manual involvement.
----

I’m probably asking a lot out of one macro, but hopefully my goal makes sense. Can anyone help?

I need a macro that will look through all the cells in a range. If the next cell's value is not equal to the previous cell's, then I need to insert a row.

I attached a sample. So, it will search the "UDF" column until the previous value is not equal to the current cell value, and insert a row after the previous cell value.

This is what I have so far :

Sub InsertRow()

Dim Counter             As Long
Dim RngCell             As Range
Dim RngCell2            As Range
    
  Do
    Counter = Counter + 1
    RngCell = Range("B" & Counter)
    RngCell2 = Range("B")
        For Each RngCell In Range("B1:B10000")
            If RngCell.Value <> RngCell2.Value Then
            RngCell2.EntireRow.Insert
                End If
        Next
  Loop While RngCell.Value < 10000
End Sub
But it's obviously not working. I get an "Object variable or With block variable not set" on this line of code:



Hi!

In all the sheets in the workbook I need to check column F for containing a cell of value "Commission". If it does, the numbers in the cells of the column following "Commission" needs to be summed together and the sum is insertet in the first blank cell following the numbers that are summed.
Also, if the sum is calculated, in column A I need insertet a text two rows following last cell containing value. It is better shown in the attached workbook. The code is as follows (but not working):

Public Sub Workbook_1()

For Each sh In ActiveWorkbook.Sheets

    With sh
        
        If IsError(Lookup("Commission", f)) = True Then Next sh
           
        Else: Range(Lookup("Commission", f), .Cells(Rows.Count, "f").End(xlUp)).Select
        
        Sum = Sum(Selection.Value)
        
            .Range(, .Cells(Rows.Count, "f").Offset(1, 0)) = Sum
                
        Range(Cells(Rows.Count, "a").End(xlUp).Row).Select
        Selection.Offset(2, 0).Value = "Insert text"
        
        End If
        
    End With

Next sh

End Sub
Any help is appreciated! Thanks in advance.


No luck finding an answer? You could always try Google.