Type Mismatch Errors 1

[Azathoth]

I'm having some type mismatch errors in my comparison statements. I'm pretty new to VB, so the syntax is rather unfamiliar. Here's the code:

Sub Macro1()

Range("A1&quot.Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

For Each Row In Selection
If Columns(1).Value = "254 Project" Then
Columns(3).Cut
ActiveCell.Offset(-3, 0).Range("A1&quot.Select
ActiveSheet.Paste
End If
If Columns(1).Value = "DPIC Project" Then
Columns(3).Cut
ActiveCell.Offset(-5, 1).Range("A1&quot.Select
ActiveSheet.Paste
End If
Next

End Sub


The fifth line is the one currently giving me problems (If Columns(1).Value...) Any improvements or suggestions are welcome. Thank you.

 

[Zathras]

Not sure where to begin. What are you trying to do?

Here are a couple of things to start with, but the entire thing probably needs rewriting.

Declare variables after the Sub Macro1() line so the compiler knows what you are trying to do:

  Dim Row As Range

In your "For Each Row In Selection" the compiler will be processing one cell at a time. Calling a single-cell range a "Row" will not help. The Selection consists of single-cells. But if you really want to, you can use

  For Each Row In Selection.Rows
    If Row.Cells(1, 1).Value = "254 Project" Then

But that's a little bit awkward. It would probably be better to define a range that consists of the first column and then process each cell in that column, referencing other cells in the same row with .Offset

 

[Azathoth]

I'm just trying to reformat a report file that's outputted in a specific way...two specific values need to be cut and put into a different place, hence the ActiveCell.Offset statements.

The previous post seems to have put me on a better track, one of my if-statements now looks like this:


If Row.Cells(1, 1).Value = "254 Project" Then
Row.Cells(1, 3).Cut
ActiveCell.Offset(-3, 0).Range("A1&quot.Select
ActiveSheet.Paste
End If

Admittedly not eloquent, but closer. Now, however, I'm getting an error: "Application-Defined or Object Defined Error" referring to the third line of that if-statement.

 

[Azathoth]

Possibly because the entire area is allready selected, and now I'm trying to select just a portion of it? Allthough I can't concieve of another way to step through the entire file.

 

[Zathras]

Play around with this. It should be pretty close to what you want:
[blue]

Option Explicit

Sub RepositionDescriptions()
Dim ColumnA As Range
Dim c As Range
  Set ColumnA = Intersect(ActiveSheet.UsedRange, Range("A:A"))
  For Each c In ColumnA
    If c.Value = "254 Project" Then
      c.Offset(0, 3).Cut c.Offset(-3, 0)
    End If
    If c.Value = "DPIC Project" Then
      c.Offset(0, 3).Cut c.Offset(-5, 1)
    End If
  Next c
  Application.CutCopyMode = False
  Set ColumnA = Nothing
End Sub

[/color]

 

[Azathoth]

No luck with that code...it hangs for a moment, then nothing happens. I appreciate the suggestion though.

 

[Zathras]

It worked just fine for me, but I had to make a few assumptions about your data. Can you post some test data and indicate what you want to have happen? (Sort of a before and after picture.)

 

[Azathoth]

Ok, data arranged as such:


00001.00 1251 Maritime Roof Fan

Label Type Value

254 Project Text 072

DPIC Project Text UF


00002.00 Ditkoff Residence, Darien, CT

Label Type Value

254 Project Text 050

DPIC Project Text C

Contact Title Text

Insurance Number 0

Profit Center Text

Proj Contact Text



Should come out looking like:

00001.00 1251 Maritime Roof Fan 072 UF

Label Type Value

254 Project Text

DPIC Project Text


00002.00 Ditkoff Residence, Darien, CT 050 C

Label Type Value

254 Project Text

DPIC Project Text

Contact Title Text

Insurance Number 0

Profit Center Text

Proj Contact Text


All lines except the one starting with the decimal number become extraneous, and can be deleted. As you can see, sometimes there are more than 2 entries for a project, which is what makes this more difficult that it should be.

 

[Zathras]

Perhaps you would rather not use VBA.

If I read your data correctly, you could put these formulas in columns D and E and then copy / paste special values to get what you want. (Put in D1 and E1 and copy down as far as you need.):
[blue]

D1: =IF(ISNUMBER(A1),VLOOKUP("254 Project",$A2:$C$65000,3,0),"")
E1: =IF(ISNUMBER(A1),VLOOKUP("DPIC Project",$A2:$C$65000,3,0),"")

[/color]
If you prefer a VBA solution, let me know and also let me know if the formulas worked to put the data where you want it. Otherwise I'll need to know more about the data and how to recognize the rows you want to update.

 

[Azathoth]

This method worked great, once I realized that the numbers in my file were actually formatted as text and changed the isnumber test to check for length of 8.

However, my file is 65,500 lines and growing (good guess), so pasting down the entire column takes forever. This leaves me with two questions:

1. Since this file will be constantly expanding, is there a way to change (in the vlookup statement) the 65000 to some sort of EOF marker? (ie the last line in the speadsheet)

2. Is there some way, possibly in the currently blank else part of your if-statement, to delete the entire line and shift the cells up if it doesn't start with that 8 character decimal number?

 

[Zathras]

Be aware that the absolute limit is 65,536 lines in an Excel spreadsheet.

I had no idea you were working with that much data or I would not have suggested using formulas that way. A macro would certainly be faster than copying formulas to 65,000 rows. I would be happy to create one for you, but I need more details.

Is all of the data changing constantly or can you design some sort of scheme where the historical data (lines with the 8 character decimal number and updated with your codes) are retained on one sheet, while new data is pasted into a new sheet, processed and then appended to the historical data?

If you are expecting to process a file with more than 65,536 lines in it, you are going to be disappointed. Can you just get additions (as described in the preceding paragraph) or will you always be getting the full load. If the latter, then Excel can't handle it. You should consider using a database such as Access.

BTW, is your data really double-spaced that way or are you just trying to make it easier to read? If it is really double-spaced, maybe you can get the provider to eliminate the blank lines for you. (Or if the data is coming from a text file, pre-process the file to remove the blanks. Where is the data coming from?)

 

[Azathoth]

I was unaware of that limitation imposed by excel...I'll have to look into alternative reporting formats. Thanks again.