Help Debugging Some VBA code

P

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
586
Office Version
  1. 2013
Platform
  1. Windows
I have some VBA code that almost working. Problem I am having is that the code is inserting a new row in the correct place, but when I try to insert some text into the newly inserted row the code puts the text in the cell in which the .Find value was found.

Also how can get the Merge command to merge colunms A I for each row it inserts

Code and Sample data below

Code: 
Sub Insert_Row_byQuarter()Dim ws As Worksheet:    Set ws = Sheets("Sheet2")
Dim LastRow As Long
Dim iNum As Integer
Dim iFind As Range
Dim mcol As String


LastRow = ws.Range("E" & Rows.Count).End(xlUp).row


For iNum = 1 To 4
    Set iFind = ws.Range("E1:E" & LastRow).Find(What:="Q" & iNum & "*", LookIn:=xlValues, LookAt:=xlWhole)
    If Not iFind Is Nothing Then
        iFind.EntireRow.Insert Shift:=xlDown
        mcol = iFind
        iFind.Value = PeriodTitle(mcol, i + 1)
        iFind.Merge
        iFind.Font.Bold = True
        iFind.HorizontalAlignment = xlCenter
        iFind.BorderAround ColorIndex:=1, Weight:=xlThin
    End If
Next iNum


End Sub

Code: 
Function PeriodTitle(ByRef period As String, row As Long) As String
Select Case Left(period, 2)
    Case Is = "Q1"
        PeriodTitle = "January - March " & Right(period, 4)
    Case Is = "Q2"
        PeriodTitle = "April - June " & Right(period, 4)
    Case Is = "Q3"
        PeriodTitle = "July - September " & Right(period, 4)
    Case Is = "Q4"
        PeriodTitle = "October - December " & Right(period, 4)
    Case Else
        PeriodTitle = "Invalid Quarter in Row " & row + 1
End Select
End Function
Excel 2010
ABCDEFGHI
Option / POJanuary - March 2013
Feedback from ClientQ1-2013
Feedback from ClientQ1-2013
Feedback from ClientQ1-2013
Feedback from ClientQ1-2013
Feedback from ClientQ1-2013
Feedback from ClientQ1-2013
Feedback from ClientQ1-2013
Feedback from ClientQ1-2013
Feedback from ClientQ1-2013
Feedback from ClientQ1-2013
Brief / PitchQ1-2013
Brief / PitchQ1-2013
Brief / PitchQ1-2013
Brief / PitchQ1-2013

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Opportunity Name[/TD]
[TD="align: center"]Account Name[/TD]
[TD="align: center"]Stage[/TD]
[TD="align: center"]Probability (%)[/TD]
[TD="align: center"]Fiscal Period[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"]Created Date[/TD]
[TD="align: center"]Opportunity Owner[/TD]
[TD="align: center"]Amount[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]90[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]23/01/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50000[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]90 Total[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]08/02/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]31[/TD]
[TD="align: right"]21/01/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4000[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]15/02/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]28000[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]13/02/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4096[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]28/01/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3483[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]38[/TD]
[TD="align: right"]14/01/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13182[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]23/01/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2886[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]30/01/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]16000[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]30/01/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]16000[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]13/02/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30 Total[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]93147[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]04/02/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1200[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]13/02/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]35000[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]01/02/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6000[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]125[/TD]
[TD="align: right"]19/10/2012[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1500[/TD]

</tbody>
Sheet2​
 
Sort by date Sort by votes
You don't need to merge for aligning across a range. Try (untested):

Code: 
        With iFind.Offset(-1)
            .Value = PeriodTitle(mcol, i + 1)
            .Font.Bold = True
            .EntireRow.Resize(1, 9).HorizontalAlignment = xlCenterAcrossSelection
            .BorderAround ColorIndex:=1, Weight:=xlThin
        End With
 
Upvote 0
Hi Andrew,

I have just run your code and it is now on the correct row, one small problem the formatting is not quite correct. It is not formatting from Column A until I but form the cell if found the .Find value column E See below (Attached image so you can see formatting)

Download link
http://we.tl/UZRiGHbQC5


UZRiGHbQC5


Excel 2010
ABCDEFGHI
January - March 2013
Option / POQ1-2013

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Opportunity Name[/TD]
[TD="align: center"]Account Name[/TD]
[TD="align: center"]Stage[/TD]
[TD="align: center"]Probability (%)[/TD]
[TD="align: center"]Fiscal Period[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"]Created Date[/TD]
[TD="align: center"]Opportunity Owner[/TD]
[TD="align: center"]Amount[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]90[/TD]

[TD="align: right"]36[/TD]
[TD="align: right"]23/01/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50000[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]90 Total[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50000[/TD]

</tbody>
Sheet2​
 
Last edited:
Upvote 0
Yes I see, you'll need to put it in column A. Try:

Code: 
        With iFind.EntireRow.Cells(1, 1).Offset(-1)
            .Value = PeriodTitle(mcol, i + 1)
            .Font.Bold = True
            .Resize(1, 9).HorizontalAlignment = xlCenterAcrossSelection
            .BorderAround ColorIndex:=1, Weight:=xlThin
        End With
 
Upvote 0
Thanks Andrew,

Your time is most appreciated

Just one very small issue the line

BorderAround ColorIndex:=1, Weight:=xlThin

Is only putting a border around Cell A not A to I on the row
 
Upvote 0
Still doing the same is only putting a border around Cell A not A to I on the row

It is not a major problem it will be fine without the border

Thanks for your time.
 
Upvote 0
Andrew

I must apologise you absolutely correct.

I don't know what i did the first time but it is working spot on now

thanks you for your time
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
207
ellison
E
T
  • Question Question
VBA help to count Daytime,Nighttime and Summary
2 3
Replies
20
Views
1K
Alex Blakenburg
A
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
213
flashgordie
F
A
  • Solved
Filter data in listbox on form based on matching part of item in two comboboxes
Replies
9
Views
246
Ali M
A
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
236
Madam_Xavier
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top