0

I know this question has been addressed in several places here, but none so far are solving my problem, and I can't think of anything else to search for.

Specifically, within an Excel macro I need to find the row number of the cell on a specific sheet that has a value that matches a variable. Then I need to use that row number to perform an action on another cell in that row. The value I'm looking for will be in Column C, then the action will be performed in Column A.

I have tried these solutions but no luck yet:

How to find the row number of a specific value in Excel using vbscript

Assigning the value of a worksheet cell to a constant

I've tried pasting my variable value into B1 as a placeholder, but I can't find a way to find the matching value elsewhere on the sheet (there will be only one other). I've tried:

Public Const SKUVALUE As String = "$B$1"

but of course that returns the quoted string, not the value of the B1 cell.

If it would help for me to explain the bigger picture of my project I will be happy to do that, but it's somewhat complicated. In short I am trying to bring content from one sheet to another sheet based on a numeric value (SKU) that is assigned to a specific row on each sheet. The row numbers will not match because of filters being applied on the first sheet. If I can mirror those filters on the second sheet then I would know the row number, but that sounds like a nightmare to implement. I can't begin to think how to do it.

I'm really at my wit's end with this. Any suggestions will be much appreciated. Thank you.

Community
  • 1
  • 1
Thea
  • 61
  • 1
  • 4
  • 8
  • 1
    How haven't those threads helped? What have you tried specifically? It sounds like some use of `Match` or a `Find` should work. Please post any code you've tried, and any errors it's throwing, or if it's not working, explain *how* it's not working. – BruceWayne Apr 12 '17 at 18:42
  • 3
    Does this really need VBA? This sounds like an issue that the [VLOOKUP function](https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1) can solve... – tigeravatar Apr 12 '17 at 19:39
  • To clarify, I tried VLOOKUP but I need to return the cell address, not its value, so I didn't think it would be helpful. The action once I have the address is to go 2 columns to the left and copy that cell's content (an image embedded in a comment in the cell), then paste that content into another sheet. So I think it's more complicated than VLOOKUP can handle. Thanks for your thoughts. – Thea Apr 12 '17 at 23:10

2 Answers2

0

You could try to use a loop to accomplish this. Depending on your data this should work. You did not provide much detail into the action to perform in column A or how your variable is declared so this is a start.

 Sub Macro1()
' This macro will loop through each row and look for your variable in column C if it finds your variable in column C it will then perform your action in column A of that same row.

Dim variable As String
variable = "insert value or cell here"

With Sheets("Sheet1")
    LR = .Cells(Rows.Count, "C").End(xlUp).Row
    For i = LR To 2 Step -1
        If .Cells(i, "C").Value = variable Then
            .Cells(i, "A").Value = "perform action here"
        End If
    Next i
End With

End Sub
Joe W
  • 111
  • 4
  • but why? the built in functions like Match, Vlookup, or the range.Find method are well suited for this sort of task -- there's no need to reinvent the wheel – David Zemens Apr 12 '17 at 23:21
0

UPDATE: My problem was with using variables correctly, and a solution appears to be:

Public Const SKUVALUE As String = "$B$1"
Public imageValue As Long
Public n As Long

Then:

Sub FindFirstInstance()
Dim ws As Excel.Worksheet
Dim FoundCell As Excel.Range
Dim imageValue As Long: imageValue = Range(SKUVALUE).Value
Set ws = ActiveSheet
Set FoundCell = ws.Range("C:C").Find(what:=imageValue, lookat:=xlWhole)
If Not FoundCell Is Nothing Then
    MsgBox (imageValue & " found in row: " & FoundCell.Row)
n = FoundCell.Row
MsgBox "n is " & n
Range("$C$1").Value = n
Else
MsgBox (imageValue & " not found")
End If
End Sub

The problem I'm continuing to have is the global/public variables not working between sheets. That's why I'm putting the value of n into C1 above, so I can grab it later. But I will keep working on that. For the moment, I think I have what I need. Many thanks.

Thea
  • 61
  • 1
  • 4
  • 8
  • *The problem I'm continuing to have is the global/public variables not working between sheets* < -- you need to scope your variables appropriately. `Public` declarations in a UserForm, Worksheet, or Class module are not the same as `Public` declarations in an ordinary code module. :) – David Zemens Apr 12 '17 at 20:21
  • This might help: http://stackoverflow.com/questions/16374253/how-to-make-excel-vba-variables-available-to-multiple-macros/16374516#16374516 – David Zemens Apr 12 '17 at 20:22
  • You can also store values in a `Name` that is accessible from any code module, or from the worksheet itself. – David Zemens Apr 12 '17 at 20:24
  • Excellent! Thank you so much. – Thea Apr 12 '17 at 22:48