How to select a column with the word "DIC" and the three colums next to it?

[cathleenturner]

Hello All
I have to get raw data from many files in 'Sheet 1'. The structure is generally the same, because the raw data follows the column with the word DIC, but it is not in the same location. How can I use a function (such as find) to
1) search for the column containing the word DIC and
2) select the column containing the word DIC and the three columns next to it, as well?
3) Copy what I have selected and paste into the masterfile

I need to copy this data and paste it into my master file, but I already have that part worked out (I am adapting code I have written for another purpose). I would like to use it in my module (VBA?). I am learning how to write code in VBA, and so far its okay. This website is very helpful.

I am using excel 2010

Thanks in advance!
Cheers,
Cat

------
The code I will be 'adapting' for this new purpose:
Sub copypastaalkdata()
' This is to get data from the worksheet name "WS2"
'and to past it into one master file


'Set the path of the workbook where you will save all data
Dim wb1 As Workbook
Set wb1 = Workbooks.Open("C:\Users\Cathleen\Google Drive\Raw Alk data\Converted_Raw_Alk_files\MasterFileALK.xlsx")




'Set the path of where you should be getting your data from
Dim folderpath As String
Dim filename As String


Dim wb As Workbook
folderpath = "C:\Users\Cathleen\Google Drive\Raw Alk data\Ready\"


If Right(folderpath, 1) <> "\" Then folderpath = "\"
filename = Dir(folderpath & "*.xlsx")


Do While filename <> ""
Dim pathname As String
usethisfile = folderpath & filename

Dim wb2 As Excel.Workbook
Set wb2 = Workbooks.Open(usethisfile)

Application.ScreenUpdating = False

'1.) Determine where the data will go in the masterfile, which is the last row of existing data
wb1.Activate
Dim lastRow As String
lastRow = wb1.Worksheets("pastehere").Cells(Rows.Count, "B").End(xlUp).Row + 1
wb1.Worksheets("pastehere").Range("B" & lastRow).Select

'2.) Activate the sourcebook, aka where you'll get your data from
wb2.Activate
Application.CutCopyMode = False 'to clear clipboard to maximize memory

'i) Get the filename
Dim strFileFullName As String
strFileFullName = wb2.FullName


'3.) Copy from specified range in wb2
wb2.Worksheets("WS2").Range("A2:D50").Copy

'4.)Paste in masterfile
wb1.Worksheets("pastehere").Range("B" & lastRow).PasteSpecial Paste:=xlPasteValues

'5.)put filename into into the masterfile, and save

wb1.Worksheets("pastehere").Range("A" & lastRow).Value = strFileFullName

wb1.Save

'd.) close the file you are getting the alk data from and move on to the next one
wb2.Close
Set wb2 = Nothing

filename = Dir
Loop
Application.ScreenUpdating = True


End Sub

 

[oldbrewer]

you can search each column in turn to find DIC (assume 5 columns and 10 rows)

for j=1 to 5
for k=1 to 10
if cells(k,j)="DIC" then cells(1,15)=j
next k
next j

 

[cathleenturner]

Hi!
Thanks for your response. I am not sure if I understand how your code searches for 'DIC', would you mind explaining what each line does? Sorry, I have just started learning VBA.

 

[XLfriend777]

Hi cathleenturner - Welcome to the forum. It is a great place to learn. An easy way to search each cell in a range is the code below. If the c.Value (the value in the cell) is equal to a certain value "DIC" you can then perform whatever action you want. The code is below and I would be glad to explain any other questions you may have.

Sub cathleenturner_find()
For Each c In Range("A1:B20") 'Adjust range as necessary
    If c.value = "DIC" Then
        'Perform actions here - example MsgBox below
        MsgBox ("The word DIC was found in Row: " & c.Row & " Column: " & c.Column)
    End If
Next
End Sub