Simple question

[Phoenix_Turn]

Hi everyone! Need some help at the moment with some vba. The situation is: One column of my spreadsheet have some listed values like: cheese, pork, bread, soda in Column 'A' so cheese ("a2"), pork("a3"), bread ("a4") and soda ("a5")


Now, i have an input box in my userform, from there i need a loop structure so that the value entered in the textbook is assessed in column a to match that data, if not then, it is offset by 1 row, and the next column is assessed whether it has the same data as well and the procedure repeats until the data entered corrsponds to the data in the cell.

Once matched it is offset by 3 columns and in it will be the data i entered in the data table.

Here's what i got:

Set rngeg = Application.ActiveWorkbook.Worksheets("sheet3").Range("a2")

strFood = txteg.Text

rngeg.select

do

rngeg.offset(1,0).select

loop until rngeg.value = strFood

rngeg.offset(0,3) = strfood


Here txtEg.txt is the name of the input

textbox is from the user form

Problem is though its not working!!!! Hope someone can help thanks!!!

 

[VoG]

Try like this

Dim Found As Range
Set Found = Columns("A").Find(what:=txteg.Text, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Offset(, 3).Value = strFood

 

[Phoenix_Turn]

Try like this

Dim Found As Range
Set Found = Columns("A").Find(what:=txteg.Text, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Offset(, 3).Value = strFood

Thanks VoG!

I was wondering if its possible to write it in just using the offset function and using a loop structure.

I just want to teach myself the basics first! thank you!!!

 

[Phoenix_Turn]

bump

 

[VoG]

With a loop (which is much less efficient)

Dim i As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("A" & i).Value = txteg.Text Then
        Range("A" & i).Offset(, 3).Value = strFood
        Exit For
    End If
Next i

 

[Phoenix_Turn]

With a loop (which is much less efficient)

Dim i As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("A" & i).Value = txteg.Text Then
        Range("A" & i).Offset(, 3).Value = strFood
        Exit For
    End If
Next i

Thanks again VoG.

Suppose that there is a inputbox and everytime i add something in, th value stored will be below existing values. the question is then how would i specify it?

Forgot to mention i made a mistake in my first post.

I was meant to say:


1) Every data entered into textbook the data is checked in the first column
2) if the cell value in column a eg A5, does not match it will move onto the next row, A6 to see if that cell value matches with what has been entered into inputbox
3) once a cell has same value as inputdata that cell is offset by 3 columns and stored there is the data entered from inputbox.

MY apologies

 

[VoG]

I think that the .Find method will work with that. If not, look at the help for .FindNext - there is a good example for finding all matches.