Copy and paste (exel)

[lordeos]

Hello,

I have to sheets ... one sheet with printers including there ip adress and name .
The second one is a report dat the counter program on the servers gives me each month (this includes the printercounter : how much color how much scanned etc etc ...).
What i want to do is . i check the ip adress from the 1st sheet with the second sheet ... when the ip adress is found he needs to copy all the counter data and paste it in the first sheet next to the printer name.

So i have to define a range of cells to copy ... i use two counters to keep track of the row where im at .
Now i get stuck at the range defining ... ill copy my code and can someone look at it please.

If var = var2 Then
Range("h" & r & "" & r).Select
Selection.Copy
Sheets("Blad1").Select
Range("i " & " 17:i" & "25").Select
ActiveSheet.Paste

r and i are the counters i use in the loops i created ... one loop to read the ipadress and then he jumps in the second loop and reads every ip on the second page ... if they match he copies the the data . But at the range defining he gives an error.

thx in advance

 

[MakeItSo]

r and i are the counters i use

So "i" is a variable. Then this line won't work:

Range("i " & " 17:i" & "25").Select

Change it to

Range(i & " 17:" & i & "25").Select

;-)
Cheers,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
HP:

http://andygalambos.de

 

[Loomah]

Hi
If i is a variable then

Range(i & " 17:" & i & "25").Select

won't work either

You'll need something like this

Range(Cells(17, i), Cells(35, i)).Select

However there is a flaw in the overall code in that you are trying to copy a row of data and paste it into a column. If this is definately what you want to do have a look at the PasteSpecial method in help, specifically Transpose.

;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[MakeItSo]

won't work either

Why not? I just tried it out and it worked fine - if i remove the extra space before the "17"... ;-)

But yes, you should rather select a cell, not a range as paste destination - and paste special, if needed.

 

[Loomah]

MakeItSo

To be honest I hadn't even noticed the extra space!

When I said it won't work I was assuming that the aim was to select a specific range of cells.

If i is a counter in a loop lets just give it a value of 2 for argument's sake....

Range(i & "17:" & i & "25").Select
will be read as
Range("217:225").Select
which will select rows 217 to 225 (or it does for me!!) which is obviously fine if that's the intention

However using the Range property with 2 arguments as I mentioned above it would be read as
Range(Cells(17, 2), Cells(35, 2)).Select
which would select the range B17:B35

As I say, it depends what the requirement is!!

At the end of the day, though, selecting a single cell, as you pointed out, would be more sensible so
Cells(17, i).Select
is all that should be needed.

It would be better still not to select anything at all but I'm not sure that's possible (haven't tried it) with PasteSpecial.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[lordeos]

I keep getting errors.

Ill paste the full source in here so you can have a look.


For i = 2 To berekeneindecellen(pagina)
var = ActiveWorkbook.ActiveSheet.Cells(i, 5).Value
MsgBox var
Set pagina = ActiveWorkbook.Sheets("test")
For r = 2 To berekeneindecellen(pagina)
var2 = ActiveWorkbook.Sheets("test").Cells(r, 2).Value
MsgBox var2
If var = var2 Then
Range(Cells(8, r), Cells(15, r)).Select
ActiveWorkbook.Range.Copy
Sheets("Blad1").Select
Range(Cells(17, i), Cells(25, i)).Select
ActiveSheet.Paste

thx

 

[PHV]

I keep getting errors
Any chance you could post the error messages ?
Anyway, how is dimmed the variable pagina ?
I don't think it's a good practice to change the value of an variable inside a loop when this variable is used to control the end of the loop:
For i = 2 To berekeneindecellen(pagina)
...
Set pagina = ActiveWorkbook.Sheets("test")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MakeItSo]

Obviously, "berekeneindecellen" is a function, which returns an integer value depending on a sheet, else

For r = 2 To berekeneindecellen(pagina)

will fire an error. Is it?
Any chance, that the error is with your function?

Besides that, PHV is totally right: Are you certain that your loop stops at the correct point and you don't end up with an index out of bounds at some point in code?

 

[lordeos]

The function calculates the last used cell for the two pages ...
the sheet is a object variable wich i put in to the function. He returns me the last used cell . This number i use in my "for loop' .
The function works and gives the result i want .
The error appears at the range statement . When the two ip's match he needs to copy some data from a few cells to a few cells on the other page .
Is at the defining of the range where the error appears.

thx for the help

Phv that the variables changes doesnt matter ... because he calculates the last used every time again ... i know this is a flaw in the code ... but it works i can change it after , but first the range thing i want to fix.

Thx for the help

 

[PHV]

Have you tried to replace this:
Range(Cells(8, r), Cells(15, r)).Select
ActiveWorkbook.Range.Copy
By this ?
Range(Cells(8, r), Cells(15, r)).Copy

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MakeItSo]

var2 = ActiveWorkbook.Sheets("test").Cells(r,2).Value
...Range(Cells(8, r), Cells(15, r)).Select

You are using r once as row parameter, the other time as column parameter. Is this intended? Do you get an out of range error?