Find specific cell, then dim its value as string

**ks100** · 01-02-2014, 03:32 PM

Need to:

1. Find column with header title "quantity" (i.e. text in first row for that column is "quantity")
2. Dim the contents of the third row of that column as a string

**Solus Rankin** · 01-02-2014, 03:40 PM

Sub ks100()
Dim l As Long
Dim strTest As String

For l = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
    If LCase(Cells(1, l).Value) = "quality" Then
        strTest = Cells(3, l).Value
    End If
Next l
End Sub

**JOHN H. DAVIS** · 01-02-2014, 03:41 PM

Perhaps:

Sub ks100zzaazzz()
Dim x As String
Application.ScreenUpdating = False
Cells(1, 1).Select
Do Until ActiveCell.Value = "Quantity"
    If ActiveCell.Column > ActiveSheet.UsedRange.Columns.Count Then Exit Do
    ActiveCell.Offset(, 1).Select
Loop
x = ActiveCell.Offset(2).Value
Application.ScreenUpdating = True
End Sub

**ks100** · 01-02-2014, 03:57 PM

Originally Posted by JOHN H. DAVIS

Perhaps:

Sub ks100zzaazzz()
Dim x As String
Application.ScreenUpdating = False
Cells(1, 1).Select
Do Until ActiveCell.Value = "Quantity"
    If ActiveCell.Column > ActiveSheet.UsedRange.Columns.Count Then Exit Do
    ActiveCell.Offset(, 1).Select
Loop
x = ActiveCell.Offset(2).Value
Application.ScreenUpdating = True
End Sub

Perfect, thank you

**JOHN H. DAVIS** · 01-02-2014, 04:04 PM

You're welcome. Glad to help out and thanks for the feedback.

**berlan** · 01-02-2014, 03:57 PM

With find function:

Sub main()

    Dim rng As Range, str As String

    Set rng = Sheets(1).Rows(1).Find("quantity", , xlValues, xlWhole)        'may need to change sheet name
    If Not rng Is Nothing Then str = rng.Offset(2).Value

End Sub

**berlan** · 01-02-2014, 04:13 PM

@JOHN H. DAVIS, you are the expert, but to minimize the interaction between VBA and Excel, generally I would not use activecell or select. Is there a specific reason for this?

**JOHN H. DAVIS** · 01-02-2014, 04:34 PM

Originally Posted by berlan

@JOHN H. DAVIS, you are the expert, but to minimize the interaction between VBA and Excel, generally I would not use activecell or select. Is there a specific reason for this?

Not really. Your code is more efficient and performs the same tasks in this circumstance. However, I find that activecell has it's place in coding and I tend to rely on it probably a bit more than I should.

**berlan** · 01-02-2014, 04:49 PM

Originally Posted by JOHN H. DAVIS

Not really. Your code is more efficient and performs the same tasks in this circumstance. However, I find that activecell has it's place in coding and I tend to rely on it probably a bit more than I should.

Okay, I see, thanks for your reply

All best

Find specific cell, then dim its value as string

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Find specific cell, then dim its value as string

Re: Find specific cell, then dim its value as string

Re: Find specific cell, then dim its value as string

Re: Find specific cell, then dim its value as string

Re: Find specific cell, then dim its value as string

Re: Find specific cell, then dim its value as string

Re: Find specific cell, then dim its value as string

Re: Find specific cell, then dim its value as string

Re: Find specific cell, then dim its value as string

Thread Information

Users Browsing this Thread

Similar Threads

VBA Find all instances of a specific string and copying the number of count in cell

Find a specific string

Find a specific string in a cell

Find a specific string in a cell

Find a specific string in a cell

Bookmarks

Bookmarks

Posting Permissions