Please Help Correct my code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I think you need something like this:

Sub mycode1()

Dim rRng As Range
With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
.Range("B1:E" & c.Row - 1).Copy
End If
End With

With Worksheets("DataAll")
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
.Cells(lastrow + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With

End Sub

HTH
 
I have a bunch of files. Data format is common in all files. It looks
like this:

Col.A ColB Col C Col D Col E

empty Data Data Data Data (this goes on until about
200 rows)

then Col A has "Ave" written and it averages in all other cols.

I have written a code to find "Ave" in col A. and copy data from one
row above up untill row one. Then go to sheet "DataAll" find last row
and paste the data.

Here is my code:

Sub mycode1()

Dim rRng As Range

rRng =
Worksheets(Sheet1.Range("A:E").Find("Ave")(A).End(xlUp)).Resize(,
5).Copy
Application.Goto Worksheets("DataAll")
Set rRng = DataAll.Range("a1:A" & Range("A" &
Rows.Count).End(xlUp).Row)

With rRng.Offset(rRng.Count, 1).Resize(1, 4).Select
ActiveSheet.paste
Selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False


End With


End Sub

It gives me 400 error.

Appreciate help.

Thank you
Syed
 
Hi,

Suggests a typing error. I copied the code direct from my posting to you
and it worked OK. Did you copy or re-type?
 
Your code is giving me syntax error at :
Cells(lastrow + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone,
Click to expand...

I bet it is, if you have a line of code ends with a comma...

..Cells(lastrow + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

is all one line, there should be no wrap to a 2nd line.

HTH,
 
Hi Toppers,
Thank you for helping. Your code is giving me syntax error at :

..Cells(lastrow + 1, 1).PasteSpecial Paste:=xlValues,
Operation:=xlNone,

Any idea?

Thanks
Syed
 
It is just a line wrap problem caused by your newsreader. Try:

Sub mycode1()

Dim c As Range
Dim rRng As Range
Dim lastRow As Long
With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
.Range("B1:E" & c.Row - 1).Copy
End If
End With

With Worksheets("DataAll")
lastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Cells(lastRow + 1, 1).PasteSpecial Paste:=xlValues _
, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub

Hope this helps
Rowan
 
See George's reply: check all the code for statement with syntax error is on
one line.

(Thanks George)
 
You are right George. But I replaced it with "_"
and it took it. But when I ran it again it gave me "compile error"
expected function or variable on (red) line
Thanks
Syed

Dim rRng As Range
With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
..Range("B1:E" & c.Row - 1).Copy
End If
End With

With Worksheets("DataAll")
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
..Cells(lastrow + 1, 1).PasteSpecial paste:=xlValues, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
End With
 
Sorry I didn't follow. What do you mean by you put it in the code? What
are you wanting it to return? Show all your code again. If you just want
the sheet name using code then somthing like this:

Range("A1").value = sheets(1).name

Regards
Rowan
 
Hi Rowan,
Thank you, it worked fine.
my next adventure:

I have following formula for worksheet:

Mid(CELL("filename", A1), Find("]", CELL("filename", A1)) + 1, 255)

This formula copies the sheet name in cell A1. I want to put this in
the code before it finds "Ave" this is just so that I can keep track
of data coming from which sheet. I put this in the code. It seems it
did'nt like CELL function.
Any idea?
Also if it works I will change my range ".Range("B1:E" & c.Row -
1).Copy"
to
"A1:E"

Thank you
Syed
 
Try:

Sub mycode11()

Dim c As Range
Dim rRng As Range
Dim lastRow As Long
Worksheets(1).Range("a2").Formula = _
"=Mid(CELL(""filename"", A1), Find(""]""," _
& "CELL(""filename"", A1)) + 1, 255)"
With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
Range("A1:E" & c.Row - 1).Copy
End If
End With

With Worksheets("DataAll")
lastRow = .Range("B" & Rows.Count).End(xlUp).Row
Cells(lastRow + 1, 1).PasteSpecial Paste:=xlValues _
, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub

Regards
Rowan
 
Rowan I am sorry for not being clear.
I am trying to insert following line:

Mid(CELL("filename", A1), Find("]", CELL("filename", A1)) + 1, 255)
This is a worksheet function.

in the same code you provided to me:

Sub mycode11()

Dim c As Range
Dim rRng As Range
Dim lastRow As Long
Formula Worksheets(1).Range("a2") = Mid(CELL("filename", A1), Find("]"
CELL("filename", A1)) + 1, 255)
With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
.Range("A1:E" & c.Row - 1).Copy
End If
End With

With Worksheets("DataAll")
lastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Cells(lastRow + 1, 1).PasteSpecial paste:=xlValues _
, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub

This will give me in cell A2 the sheet name I am copying the dat
from.
Just so that I can keep trak of data I am copying.
I hope its clear now. When I run this code with that function (in red
it says "Sub or Function not defined"

Thanks
Sye
 
Hi Rowan,
Thank you so much for you help.
Everything works fine. Appreciate your help.
Thank you
syed
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Object variable or with Block variable not set 9
Run two macros for two different sheets 11
Run-Time Error "9". Subscript out of range 3
ERROR 424" OBJECT REQUIRED 1
Type mismatch error 1
conso macro 0
Trying to "clean up" some code... 1
lastrow function help 6

Back
Top