Help with vba code

Topic

New Reply

Hi,

I am struggling to compile some code that is technically beyond my capabilities, so would really appreciate some help.

I have added comments to the code below that hopefully explain what I am attempting to do.

Code:

Sub Test()

Dim amt As Worksheet, ath As Worksheet, aat As Worksheet
Dim r As Long, s As Long
Dim x As Range, y As Range, z As Range
Dim j As String

	Set amt = Worksheets("Admin MGR History")
	Set ath = Worksheets("Admin TFR History")
	Set aat = Worksheets("Admin Action TFR'S")
	
	r = aat.Cells(Rows.Count, 3).End(xlUp).Row
	
	'irrelevant code removed
		
	j = aat.Range("E5")
	
	'Now I need to capture the row number that j is on in amt, column A, rows 2 : endxlup
	'This becomes the destination row

	Set x = aat.Range("K35:K" & r)
	For Each y In x
	
	'Copy y.offset(0,13) and paste as special values to the first blank cell in the range of >
		'either BZ, CC, CF, CI or CL on the destination row
	'Then copy y.offset(0,17) and paste as special values to the first blank cell in the range of >
		'either CA, CD, CG, CJ or CM on the destination row
		
	'Find y on the destination row (which will be within the range of columns E:P) and >
		'replace it with y.offset(0, -8)
		
	Next

'irrelevant code removed

End Sub

Thanks

Reply | Quote

Replies

It is appreciated that:

[*]the material on which you’re working may be confidential
[*]you don’t want to burden us with excessive detail

Nonetheless, it could be helpful if you could post a sample workbook – as (for me at least) a wider perspective on your problem could be helpful.

HTH

Reply | Quote

It is appreciated that:

[*]the material on which you’re working may be confidential
[*]you don’t want to burden us with excessive detail

Nonetheless, it could be helpful if you could post a sample workbook – as (for me at least) a wider perspective on your problem could be helpful.

HTH

Ok, Thanks. Not majorly confidential, but there is a lot of excess in the file. I am trying to sort out a stripped down copyof the 16mb file, after removing the vast majority (about 80%), the file size is still 13mb.

I’ll keep trying…..

Reply | Quote

Here goes….

I have had to remove the vast majority of the formula’s due to the fact that they were looking at one sheet that is 11mb and I could not reduce it. That sheet contains a web query.

Hopefully this will be enough to give the general idea.

Thanks

Reply | Quote

‘Now I need to capture the row number that j is on in amt, column A, rows 2 : endxlup
‘This becomes the destination row

Hi Nathan

Can you further explain “amt, column A, rows 2 : endxlup”

Reply | Quote

Hi Don,

This has moved on somewhat (Very slowly) and I am (slightly) past that point.

Hopefully the attached explains where I am now stuck.

Thanks for any help.

Reply | Quote

Range(p, q) is the rectangular range with p and q as opposite corners. If you want to copy two non-adjacent cells, you must copy each of them individually.

Reply | Quote

So a non contiguous range cannot be copied? I was not aware of that.

Reply | Quote

You *can* copy a non-contiguous range, but not with the syntax you tried.
If you have two ranges p and q that represent non-adjacent cells, you’d have to use

Union(p, q).Copy Destination:=r

But even if p and q are non-adjacent, they will be copied into adjacent cells. There is no way to copy for example A1 and E1 into A5 and E5 in one go.

Reply | Quote

Thanks Hans, I’ve only just seen your post, I was beavering away and am now nigh on complete.

I am struggling with the very last little bit and would appreciate any help that loungers can offer.

Updated:

Reply | Quote

Code:

Set w = amt.Range("E" & p & ":P" & p).Find(What:=y.Value, LookIn:=xlValues, LookAt:=xlWhole)
	w.Value = y.Offset(0, -8)

Got there eventually.

Reply | Quote