PDA

View Full Version : Wont let me select range



jibbercrap
01-22-2009, 10:06 AM
Hey,

I am using the following bit of code to try to copy and paste some information based on if a value is found or not:


Dim lookin As Range
Dim found As Variant
Set lookin = Sheets(comptype).Columns("B:B")
Set found = lookin.Find(size, LookAt:=xlWhole, MatchByte:=False)
If found Is Nothing Then
Exit Sub
Else
Sheets(comptype).Activate
Range("c2:g2").Select
Selection.Copy
f = b + 11
Worksheets("sheet1").Activate
Range(Cells(a, f), Cells(a, f).Offset(5, 0)).Select
Selection.Paste
End If

Neither of my range selections following the "else" statement work

ANy ideas?

Edit Lucas: vba tags added to code. You can select your code when posting and hit the vba button to format it for the forum.

lucas
01-22-2009, 11:15 AM
I see lots wrong with it.

In the set lookin line you call out sheets(comptype)......should have parenthesis like this Sheets("comptype")

You do the same thing below the first else statement.

in this line Range(Cells(a, f)......etc. you should have parenthesis around the letter a.....

Why not just tell us what you are trying to do? Looks like you are looking in column B of sheet comptype for the word size. If found you want to copy range c2 to g2 of comptype to sheet1 but I don't understand where on sheet 1 you are trying to paste.....

jibbercrap
01-22-2009, 11:30 AM
comptype is a string, that is selected elsewhere...to pick different sheets when needed. That works fine in the other part of the program. Im trying to copy some data based on if the value is found in "comptype" onto sheet 1, based on a cell pointer that is automatically incremented elsewhere.

A,B are "reference points" and get incremented to point at different cell locations.

Im used to programming in C and ASM, so Im having difficulty getting my head around VBA.

lucas
01-22-2009, 11:44 AM
Why not just tell us what you are trying to do? Looks like you are looking in column B of sheet comptype for the word size. If found you want to copy range c2 to g2 of comptype to sheet1 but I don't understand where on sheet 1 you are trying to paste.....

jibbercrap
01-22-2009, 11:48 AM
im trying to write a macro that will take part #s, break them apart, then for each part of the part # find the related data and enter it into a different sheet for other purposes.

Problem is, is that we use parts from varying manufacturers, so theres no set structure.

Im trying to paste it in Sheet 1, at location A,B.

A and B are integers, representing row and column. A gets incremented and therefor points to the next part # to work on.

Also I use A so that the other part of the program knows which row to enter the data into. F is used to offset B to the appropriate column.

lucas
01-22-2009, 12:14 PM
I'm still not following you exactly so I'm going to post an example that looks for a partial string in column a of one sheet...capital or small letters and if found it copies the entire row to the next blank row of sheet 2.

May not be exactly what you are looking for but it might give you some ideas.....

jibbercrap
01-22-2009, 12:29 PM
Thanks that gave me some ideas to try out.

I want to try this:


dim szCopy, szPaste as Range

Set szCopy = sheets(comptype).Range("c2:g2")
szCopy.select
selection.copy
sheets("sheet1").activate
f=b+14
cells(a,f).select
Set szPaste = sheets("sheet1").Range(ActiveCell, ActiveCell.Offset(0,5))
szpaste.select
slection.paste

will something like that work?

remeber, that I have to use A,B to use as a pointer, or my program will never know where it is.

lucas
01-22-2009, 12:38 PM
I don't understand the pointer....can you clarify where that range comes from to begin with?

I mean they have to be a cell, right?

ps. please edit your thread and add the vba tags to your code.

jibbercrap
01-22-2009, 12:45 PM
edited

jibbercrap
01-22-2009, 12:47 PM
I attached the program with test variables in the appropriate column.

Click the button and type "resistor"

The error will come up.

*editted* uploaded the wrong version, that one i was moving things around.

lucas
01-22-2009, 01:01 PM
In the command button code, what do you think this part is doing?


a = 1
b = 4

jibbercrap
01-22-2009, 01:03 PM
I had that there to define what address I wanted to use as the initial starting point for cells(a,b)

I am going to move it later, I just have it there in the mean time while Im playing with things.

lucas
01-22-2009, 01:08 PM
How is FindSeriesresistor supposed to know that?

jibbercrap
01-22-2009, 01:14 PM
in findseriesresistors theres a cells(a,b) function. I set a,b as public integers.

In C or ASM, if you want to move information into a loop, you store it as a variable before the loop starts, then the loop can go out and fetch that data. Now I dont know if VBA works in the same way.

It worked earlier before I added the other loop. I had it just moving information from column d to comumn e on the same worksheet, and it was using that a, b designator to select its starting point.

Ive only started using VBA the past couple weeks, trying to learn how it handles things.

I just know that when I set a="whatever", and b="whatever" before findresistorseries started, it was able to use that information inside the "sub" in the "Cells"function.