1

I have a code sheet for different digits that is being used for certain calculations. I need to select the entire column based on the first value in the top row. I'm able to select the entire column based on the range but is there a VBA lookup function that looks up for a value specified in it, and compares with the value in defined cell range, and allows us to copy the entire column? Here's what I have done so far:

Columns("F:F").Select
Selection.Copy
Columns("A:A").Select
Selection.Insert Shift:=xlToRight

Suppose I have a value called "string1", so in my case, "string1" is in the address "F1" which is why I have copied the "F" column & pasting it in front of all other columns. Similarly, if the value is changed to "string2" which is at the address "G1" then column "G" needs to be pasted in the front of the sheet.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Starky
  • 135
  • 6
  • 2
    Use `Range.Find` or `Application.Match` to find the string in question in row one. Using `Find`, if the string is found, the result is a `Range` you can call `.EntireColumn` on. If using `Match`, if the string is found, the result is the column index. – BigBen Jul 07 '21 at 03:25

1 Answers1

0

Based on BigBen's suggestion here's what did my work. I hope it helps all the newbies in VBA Macros.

Dim c As Range
With Worksheets(1)
    Set c = .Range("A1:XFD1").Find("string1", LookIn:=xlValues)
    Range(c.Address).EntireColumn.Select
    Selection.Copy
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
End With

Because I only want to search on the first row of the sheet so I used that range but it can be changed to any value depending on the requirement. The Find function returns the range (variable c) which can be used for desired selection & then it can be used in a way we want.

Edit: (To avoid selecting cells)

Dim c As Range
Set c = Worksheets(1).Range("A1:XFD1").Find("string1", LookIn:=xlValues)
c.EntireColumn.Copy
Columns("A:A").Insert Shift:=xlToRight
Super Symmetry
  • 2,837
  • 1
  • 6
  • 17
Starky
  • 135
  • 6
  • 1
    Please, pay attention to [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Алексей Р Jul 07 '21 at 05:35
  • 1
    To make it work in any version of `Excel` you should replace `.Range("A1:XFD1")` with `.Rows(1)`. `Application.Match` may be more suitable since you're looking for one (the first) occurrence. The `Find` method has a few more arguments for this case most notably the omitted `LookAt` argument. If `c` ends up to be nothing ('no match'), an error will occur. Use something like `If c Is Nothing Then Exit Sub`. You forgot the dot in `.Range(c.Address)` which should be replaced with `c` anyway. In the edit, you should qualify `Columns("A:A")` (long for `Columns("A")`) i.e. `Worksheets(1).Columns...`. – VBasic2008 Jul 07 '21 at 09:21