Posts by allancar

Have solved mystery . I had manually entered two lines of Data in the "database" when I removed those and populated from userform/textbioxes every thing works fine. Looks as if combobox will also do the trick

but still cannot get the following to work. keeps returning a tick in "Female" optionbutton. And still not not sure where it should fit in first code

1. If ws.Cells(i, 26).Value = "Male" Then _
2. OptionButton1.Value = True
3. Else ws.Cells(i, 26).Value = "Female" Then _
4. OptionButton2.Value = True

Yes my logic is a little illogical. Have looked at comboboxes and think I can make it work in this situation. Thanks for that.

However, for my education, I would like to know where I had gone wrong in the original code.

Thanks for that Roy but due to the way I have set up the workbook , I am trying to present everything in a uniform way. Userforms with textboxes suit this situation "presentation wise" better. The same userform is used to save the data in the first instance and I want the user upon entering the name be able to immediately see any existing record.

thanks again for you help

This is probably very simple but this novice can.t see where i'm going wrong

I have a Userform with a number of Textboxes that are filled with data from a separate sheet. On inputing a value(in this case a name) in Textbox1 the code looks in column 22 on another sheet and when a match is found takes corresponding data to the left and fills each separate Textbox. All works fine except it does not find or pick up the first two rows of data.

If the data is as follows:

Col 22 col23 col 24 etc

row 3 Allan Data Data ....

row4 Janet Data ''

5 David '' ''

6 Bob '' ''

7 John ' ''

And I enter David,Bob or John all text boxes are successfully filled but if I enter Allan or Janet there is no response

starts in row is 3 and column 22 (v).

Below is code

Dim ws As Worksheet
Set ws = Worksheets("info")
lastrow = ws.Cells(Rows.Count, 22).End(xlUp).Row

For i = 3 To lastrow

If ws.Cells(i, 22).Value = TextBox1 Then _

Me.TextBox2 = ws.Cells(i, 23).Value
Me.TextBox3 = ws.Cells(i, 24).Value
Me.TextBox4 = ws.Cells(i, 25).Value
Me.TextBox5 = ws.Cells(i, 27).Value
Me.TextBox6 = ws.Cells(i, 28).Value
Me.TextBox7 = ws.Cells(i, 29).Value
Me.TextBox8 = ws.Cells(i, 30).Value
Me.TextBox10 = ws.Cells(i, 31).Value
Me.TextBox11 = ws.Cells(i, 32).Value
Me.TextBox13 = ws.Cells(i, 33).Value
Me.TextBox12 = ws.Cells(i, 34).Value

End If
Next
End Sub

I am also trying to populate two option buttons by using the following but are not sure if this code is correct or where too insert it in above code

If ws.Cells(i, 26).Value = "Male" Then _
OptionButton1.Value = True
Else ws.Cells(i, 26).Value = "Female" Then _
OptionButton2.Value = True

Any assistance is gratefully accepted

Have installed the code in the workbook and with one or two small changes it is working very well .

I changed active cell range,

and Played around with this section of main code

Cells(found.Row, found.Column + 1), Sheets("Data").Cells(found.Row, found.Column-1))

changed to

Cells(found.Row, found.Column + 1), Sheets("Data").Cells(found.Row, found.Column)

Also removed shift:=xlUp after Delete as Delete automatically moved the row up

Didn't use the section of code that cleared the actives cell but just refreshed the tabs.

I also changed a number of formulas to use OFFSET to avoid problems created when shifting cells up.

Have attached final workbook for your perusal.

The RANGE FIND solution always looked like a can of wiggle worms but now I at least have some idea how it works.

Just got to sort out printing a report.....

thank you for your invaluable assistance.

copy for ozgrid 3.xlsm

hi

I have set this up so inputs from the "add to library" boxes on the "Gearlist" worksheet are saved to the "Gear library" on the Data worksheet and also to the various tabs on the " Library list" in the Gearlist worksheet. I then can select an item in this list (using the various tabs ) click Add and it is transposed to the corresponding tab on the right (Pack List).

What I want to do now is select an item from that Pack List (Gearlist worksheet) and then on pressing the Remove button delete that record from the" Pack Contents List" in Data worksheet and also then move any data below up 1 place. The item will still remain in the Gear Library for future select.

If I use the reference cell (m21) to identify which Tab the item is in and adapt your code as follows do you think this would work.

Rnge = Range("m21").Value

if Rnge="pack" then

Sheets("Data").Range("ppack" & ActiveCell.Row).Delete Shift:=xlUp

Elseif rnge="shelter" then

Sheets("Data").Range("pshelter" & ActiveCell.Row).Delete Shift:=xlUp

ect ect

or is there an easier way

Thanks

copy for ozgrid 2nd.xlsm

Thanks for your help Carim. Code does sort off work but was causing problems with the way I had store the data (stacked in vertical ranges) column delete was mucking up any data store below . so I have place data ranges in a horizontal fashion. I have attached the worksheets in question so you can see better want i am trying to achieve. when I select a item in the pack list block of tabs and click the remove button the corresponding entry in the "Data" Packlist should be delete and any remaining data move up one row. it is not necessary now for the selected entry in the Pack list Tab to be delete as this will happen on an update happens (when this code is written)

Please forgive me for any rough coding in the worksheets, as I am a complete beginner.

thanks again for you assistance.

sorry roy but can't seem to find how to post a workbook. Was hoping my last post would help show what i am trying to do

Thanks Carim for your code but the idea is for only the cells containing the item selected be clear not entire rows (as this will clear other data).

Furthermore the selected cell will be in worksheet "stocklist" and the targeted cell will be in worksheet"data"

I also want to using a command button click to instigate the code.

Thanks for having a look at this. I was using vlookup as i was thinking that with its use i could find the corresponding entry in the 2nd worksheet and then delete its contents. However if i am incorrect and there is a better way to achieve the result I'm after I will be please to be corrected as I am a total newbie.

Below is a display of what I am trying to achieve

Worksheet "Data" ----|---- Worksheet "stocklist"

Range "ppack"-----------|---- Range n22:n40

.

item1-----------------------|---- item1

item2-----------------------|---- item2 selected item

item3-----------------------|---- item3

item4-----------------------|---- item4

command button click

Outcome

Worksheet "Data"----|---- Worksheet "stocklist"

Range "ppack"---------|---- Range n22:n40

item1----------------------|---- item1

item3 ---------------------|---- item3 Item has been removed and cells has moved up
item4 ---------------------|---- item4

Is it possible to move up only the contents of the two ranges and not any cells below and outside their ranges?

thanks again for your assistance

Hi all, I have setup 2 worksheets one containing Data("Data") and one ("stocklist") displaying selected items from the data sheet. What I am trying to achieve is on selecting an item in the display list (Worksheets.Stocklist.Range(n22:n40) and using a command button delete that selection along with its listing in the Data sheet in the Range("ppack"). Is it also possible to move remaining data in only the corresponding ranges to fill empty cell?

I have been trying ,with no success, to use vlookup as it is only a small data set.

Thanks for that works fine. I also tried the following code line which also worked

ActiveCell.Copy Worksheets("Data").Range("ppack").Find("")

Thanks again for your assistance

Moderation Edit: Please not I have put your code in a special Code Box which makes the code easier to read and copy. Please use them in future, watch this 60 second video to see how

Posting Code on Ozgrid

Hi folks a total newbie here. i am having problems using Activecell. Am trying to get, through the use of a command button, the value of a selected cell to copy to another sheet.

Have check the first part of code and it works fine but the second part returns a Error >>. Object doesn't support this property or method.

I have tried the varies follow lines with no luck (not all at same time) '

What am I missing?

Some examples of what I have tried.

Worksheets("Data").Range("ppack").Find("").Value = Worksheets("Gearlist").ActiveCell.Value
Worksheets("Data").Range("ppack").Find("").Value = Worksheets("Gearlist").Range(ActiveCell.Value).Value
Worksheets("Data").Range("ppack").Find("").Value = Worksheets("Gearlist").Range(ActiveCell.Value).Value
Worksheets("Data").Range("ppack").Find("").Value = Worksheets("Gearlist").Range(ActiveCell.ActiveCell.Offset()).Value

Thanks in Advance