Type Mismatch error

[excelliot]

Hi im getting type mismatch error for follo code

can any one help me to find error in foll code



Sub Txtmove()
Dim i As Integer
Dim dealer As Long
Dim qty1 As Integer
Dim qty2 As Integer
ActiveCell("a6").Select
i = 6
For i = 6 To 18144
If ActiveCell = "" Then
ActiveCell.Offset(1, 0).Select
i = i + 1
Else
Actvecell.Offset(0, 4).Select
dealer = ActiveCell
ActiveCell.Offset(0, -2).Select
qty1 = ActiveCell
ActiveCell.Offset(0, 1).Select
qty2 = "." & Left(ActiveCell, 3)
ActiveCell.Offset(0, 5).Select
ActiveCell.Value = qty1 + qty2
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = dealer
End If
Next
End Sub

[Ivan F Moala]

Use Option Explicit to catch the typos
Look it up in Help



Option Explicit

Sub Txtmove()
Dim i As Integer
Dim dealer As Long
Dim qty1 As Integer
Dim qty2 As Integer
Range("a6").Select
i = 6
For i = 6 To 18144
If ActiveCell = "" Then
ActiveCell.Offset(1, 0).Select
i = i + 1
Else
ActiveCell.Offset(0, 4).Select
dealer = ActiveCell
ActiveCell.Offset(0, -2).Select
qty1 = ActiveCell
ActiveCell.Offset(0, 1).Select
qty2 = "." & Left(ActiveCell, 3)
ActiveCell.Offset(0, 5).Select
ActiveCell.Value = qty1 + qty2
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = dealer
End If
Next
End Sub

[excelliot]

not solved yet

[Bob Phillips]

Excelliot,

As well as the typos, you have a few other problems.

You unnecessarily initialise i (the For loop does that by default).

There is no need for all of the selecting, it makes it inefficient, and difficult to see which cells are being changed.

You seem to increment i unnecessarily if the A cell is empty.

You try to load an integer variable with a string value (qty2).

What exactly are you trying to do in this area?

[Ivan F Moala]

not solved yet

What's not solved yet .... same error ?
What line does it error @
As XLD has said what are you trying to do?? as your code does a lot of
selecting and looping...............

[excelliot]

See attched file with details for more explantion.

[Bob Phillips]

Try this mate



Option Explicit

Private Sub CommandButton1_Click()
Dim iCol As Long
Dim iBlockStart As Long
Dim iStart As Long
Dim iEnd As Long
Dim iRawRows As Long
Dim iFinNext As Long
Dim iFinRows As Long
iFinNext = 4
iBlockStart = 4
With Raw
Do
iRawRows = 0: iFinRows = 0
'column A
iStart = iBlockStart
Do Until .Cells(iStart, "A").Value <> ""
iStart = iStart + 1
Loop
If .Cells(iStart + 1, "A").Value = "" Then
iEnd = iStart
Else
iEnd = .Cells(iStart, "A").End(xlDown).Row
End If
iRawRows = Application.Max(iRawRows, iEnd - iBlockStart + 1)
iFinRows = Application.Max(iFinRows, iEnd - iStart + 1)
.Range(.Cells(iStart, "A"), .Cells(iEnd, "A")).Copy _
Destination:=Finished.Cells(iFinNext, "A")
'column B
iStart = iBlockStart
Do Until .Cells(iStart, "B").Value <> ""
iStart = iStart + 1
Loop
If .Cells(iStart + 1, "B").Value = "" Then
iEnd = iStart
Else
iEnd = .Cells(iStart, "B").End(xlDown).Row
End If
iRawRows = Application.Max(iRawRows, iEnd - iBlockStart + 1)
iFinRows = Application.Max(iFinRows, iEnd - iStart + 1)
.Range(.Cells(iStart, "B"), .Cells(iEnd, "B")).Copy _
Destination:=Finished.Cells(iFinNext, "B")
'column C:L
iStart = iBlockStart
Do Until .Cells(iStart, "C").Value <> ""
iStart = iStart + 1
Loop
If .Cells(iStart + 1, "C").Value = "" Then
iEnd = iStart
Else
iEnd = .Cells(iStart, "C").End(xlDown).Row
End If
iRawRows = Application.Max(iRawRows, iEnd - iBlockStart + 1)
iFinRows = Application.Max(iFinRows, iEnd - iStart + 1)
.Range(.Cells(iStart, "C"), .Cells(iEnd, "L")).Copy _
Destination:=Finished.Cells(iFinNext, "C")
iBlockStart = iBlockStart + iRawRows
iFinNext = iFinNext + iFinRows
Loop Until .Cells(iBlockStart, "A").Value = ""
End With
End Sub

[excelliot]

See attched file with details for more explantion.

Sorry wrong file attched pls check this file

[excelliot]

Sorry wrong file attched pls check this file
Please help me to solve error showed in this xl macro in this file

[Justinlabenne]

Your getting Type Mismatch errors in your code because the ActiveCell's being selected and their values that are attempting to be stored into your variables don't coincide.

When I run your code through, it ends up on a cell with text and it tries to store the value of it in a numeric data type (long)

I did not attempt a fix because I don't know what text you are wanting to move where, I just wasn't sure so, if you can explain a bit about what text should be moving from what cells or columns to what locations, something can be whipped up for you.:thumb

[excelliot]

Your getting Type Mismatch errors in your code because the ActiveCell's being selected and their values that are attempting to be stored into your variables don't coincide.

When I run your code through, it ends up on a cell with text and it tries to store the value of it in a numeric data type (long)

I did not attempt a fix because I don't know what text you are wanting to move where, I just wasn't sure so, if you can explain a bit about what text should be moving from what cells or columns to what locations, something can be whipped up for you.:thumb

Pls see xl file in sub-thread no. 8 in current thread, in which first sheet gives info about data to be moved from cell to which cell

[Norie]

Is this line causing the error?


qty2 = "." & Left(ActiveCell, 3)
If it is it's because you are trying to assign a string value to an integer.

[Norie]

Does this work?


Sub Txtmove()
Dim dealer As String
Dim qty1 As String
Dim qty2 As String
Dim rng As Range
Set rng = Range("A7")
While rng.Value <> ""

If rng.Value <> "" Then
dealer = rng.Offset(0, 4)
qty1 = rng.Offset(0, 2)
qty2 = "." & Left(rng.Offset(0, 3), 3)

rng.Offset(0, 8) = qty1 + qty2
rng.Offset(0, 9) = dealer

End If
Set rng = rng.Offset(6, 0)
Wend

End Sub

[Norie]

Sub Txtmove()
Dim rng As Range
Set rng = Range("A7")
While rng.Value <> ""

If rng.Value <> "" Then
rng.Offset(0, 8) = rng.Offset(0, 2) & "." & Left(rng.Offset(0, 3), 3)
rng.Offset(0, 9) = rng.Offset(0, 4)

End If
Set rng = rng.Offset(6, 0)
Wend

End Sub

[excelliot]

Thanks

In given example row was at the offset of 6

but in my case it may be for more or less

any help on it

thanks in advance:hi:

[xls]

Thanks

In given example row was at the offset of 6

but in my case it may be for more or less

any help on it

thanks in advance:hi:

Try this

First i must congratulate xld for good work on earlier excel file

and secondly to norie for easy code which have override the title of thread.


Option Explicit

Sub Txtmove_xls()
Dim i As Long
Dim iStartRow As Long
Dim iLastRow As Long
Dim rng As Range
iStartRow = 7
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A" & iStartRow)
For i = iStartRow To iLastRow
On Error Resume Next
' Set rng = Range("A7")
'While rng.Value <> ""
If rng.Value <> "" Then
rng.Offset(0, 8) = rng.Offset(0, 2) & "." & Left(rng.Offset(0, 3), 3)
rng.Offset(0, 9) = rng.Offset(0, 4)
rng.Offset(0, 10) = Trim(Mid(rng.Offset(0, 3), 4, 10))
'Else
'do nothing
End If
Set rng = rng.Offset(1, 0)
' Wend
Next
End Sub


If any error in it u r welcome to notify.

I have substitited do while with for next
:thumb

[Norie]

You have Option Explicit inside the procedure :) but apart from that it works fine.