Run-time error '13': Type mismatch

[Lee Jeffery]

Excel 97 on WINNT.

I have a number of rows of data in a report where each row i
identified as part of a 'group' by the use of a 2 letter code in colum
A.

To make it easier to action the information contained in the report,
would like to insert a blank line between each group where the column
code is HA, then HB, HC, etc.

I found some code which I adapted to look for these values and insert
blank row but it gives me the run-time error on the line:
.Offset(1).EntireRow.Resize(.Value - 1).Insert
The whole code is below:
Sub InsertRows()
Dim i As Long
Dim LastRow As Long
Dim WS As Worksheet

Set WS = ActiveWorkbook.Sheets("Sheet1")
LastRow = WS.Cells(Rows.Count, "A").End(xlUp).Row

For i = LastRow To 1 Step -1
With WS.Cells(i, "A")
If .Value = "HA" Or .Value = "HB" Or .Value = "HC" Or .Value = "HE" O
.Value = "HF" _
Or .Value = "HG" Or .Value = "HJ" Or .Value = "HP" Or .Value = "HM
Then
.Offset(1).EntireRow.Resize(.Value - 1).Insert
End If
End With
Next i
End Sub

I would appreciate any suggestions over where I have gone wrong
please. I'm not very good at this!

While I'm asking, can someone steer me in the right direction fo
saving a file using a date, please? I can do this using today's dat
as "570 report " & Format(Now, "dd-mm-yyyy") & ".xls". I need to us
the end date of a period the report relates to which is every secon
Wednesday (the next one would be 28 July, 2004). Do I need to set u
some form of calendar lookup function?

Thanks in advance.

Lee.
It's cool in Tasmania

 

[Lee Jeffery]

Now that I've pulled my head out of the paper bag of embarrassment,
have solved my problem and re-written the row insertion stuff. It'
very clumsy but it works when trying to identify a value and insert
row to separate data. It goes like this:
Sub AddRow()
Dim k As Long 'used k because I've already got i doing something els
and VBA didn't like me using it again
Dim LastRow As Long
Dim WS As Worksheet

Set WS = ActiveWorkbook.Sheets("Sheet1")
LastRow = WS.Cells(Rows.Count, "A").End(xlUp).Row

For k = 2 To LastRow 'Row 2 because of headers in Row 1
With WS.Cells(k, "A")
If .Value = "HB" Then
.Offset().EntireRow.Insert 'No 1 or -1 needed in Offset
k = LastRow
LastRow = LastRow + 1
End If
End With
Next

For k = 2 To LastRow
With WS.Cells(k, "A")
If .Value = "HC" Then
.Offset().EntireRow.Insert
k = LastRow
LastRow = LastRow + 1
End If
End With
Next 'and so on for each different value in Column A
End Sub

If anyone has a suggestion on how to shorten this, I would be ver
grateful.

I would still like an answer to my original query about saving a fil
with a specific date please. See earlier part of this thread.

Thanks in advance.
Lee

 

[Dave Peterson]

Sometimes the number of If/then/elseif's gets too big to see what's happening.

But you have another choice. Take a look at "select case" in VBA's help:

Option Explicit
Sub AddRow()
Dim iRow As Long
Dim LastRow As Long
Dim WS As Worksheet

Set WS = ActiveWorkbook.Sheets("Sheet1")
LastRow = WS.Cells(Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To 2 Step -1
With WS.Cells(iRow, "A")
Select Case UCase(.Value)
Case Is = "HA", "HB", "HC", "HD", "HE"
.EntireRow.Insert
'.RowHeight = .RowHeight * 2
End Select
End With
Next iRow
End Sub

And personally, I think I'd stay away from inserting the row. Blank rows can
cause trouble with stuff down the
road--filtering/pivottables/subtotals/charts...

But it's never bothered me to either double the rowheight or even add a border
to make a grouping stand out more.

(And I'm not sure I got all the values (HA, HB, ...))

Another way (not using Select Case):

With WS.Cells(iRow, "A")
If InStr(1, ".HA.HB.HC.HD.HE.HF.", "." & .Value & ".", vbTextCompare) > 0 _
Then
.EntireRow.Insert
'.RowHeight = .RowHeight * 2
End If
End With

It's usually lots easier to start at the bottom and work your way up when you're
inserting or deleting rows. But if you're just adding borders or adjustting
rowheights, then working in either direction is the same.

 

[Lee Jeffery]

Dave,

Thank you for your input. When I ran the first bit of code yo
supplied, it inserted a row between each record which wasn't quite wha
I was after. All I'm trying to do is insert a blank row when the valu
in column A changes from HA to HB then HB to HC and so on. The reaso
for this is that this report will ultimately be printed out so tha
action can be taken to investigate the information contained in th
report through another application. It just makes some of this easie
where there is clear definintion between the "groups" which are define
by the codes of HA, HB, etc.

I have very limited knowledge of both Excel and VBA, however, I'm
considered the "expert" by those I work with which might give you som
idea of their capabilities when working with Excel. I am automating th
formatting of this report to reduce the amount of time I need to spen
showing others how to do it manually as many of them totally lac
confidence and understanding of basic application functions. As thi
report only turns up once each fortnight, what they learn today i
totally forgotten in two weeks' time. If I can set up macro toolbars s
that one click will do all this for them, this will be a majo
productivity improvement. This should give me time to work on som
basic computer usage modules to assist these people in developing thei
knowledge of most things "computerish".

Are you able to assist with my other query about automatically savin
an Excel document with a specific date? I can already do this using th
date of the day when the doc is saved. At the moment I am using thi
and then manually navigating to the relevant directory and amending th
date. The true date on the saved file should be the date relevant t
the end of a specific period. This is always a Wednesday and occur
every two weeks. The next due date will be 28 July. Is there a calenda
lookup function I can link to and then specify which date to look for?

Thanks again.

Lee

 

[Dave Peterson]

I'd still increase the rowheight instead of inserting a row, but...

Option Explicit
Sub AddRow()
Dim iRow As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim WS As Worksheet

Set WS = ActiveWorkbook.Sheets("Sheet1")

With WS
FirstRow = 2
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
'do nothing
Else
.Rows(iRow).Insert
'.Rows(iRow).RowHeight = .Rows(iRow).RowHeight * 2
End If
Next iRow
End With
End Sub

And you say that you want to use every second Wednesday. But that depends on
what Wednesday that means and how you determine it.

There are formulas that can help:

Dim myDate As Date
myDate = Date + 11 - Weekday(Date)

Will return some wednesday--but it'll depend on what today's date is. (Don't
use this without testing--it may not be the wednesday you want.)

 

[Lee Jeffery]

Dave,

Thank you very much for all your assistance.

As I am really not very good at this and don't understand a lot of th
syntax and methods of building code, I am not having a great deal o
success with your date suggestion.

What I currently have for the file location and name format is:
"G:\ER\570 report " & Format(Now, "dd-mm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

How do I use your suggested code, please? I don't have a problem wit
which Wednesday I get returned in the date as this report is onl
generated on either the Monday or Tuesday immediately prior to th
required date so the Wednesday will always be correct.

Sorry to be a bother. Many thanks.

Lee:

 

[Dave Peterson]

gotcha:

...., "G:\ER\570 report " & _
Format(date+4-weekday(date), "dd-mm-yyyy") & ".xls", _
....

 

[Lee Jeffery]

Dave,

Fantastic! I knew a genius out there somewhere would be able to hel
me! This works like a dream.

Thanks again.

Lee:

 

[Dave Peterson]

And since you couldn't find him/her, I was there to help!

Glad it worked for you.