Excel VBA - Define value as cell position, copy below same cell position, paste

Question

So I have this code for visual basic in excel:

Sub Tele()

    Dim rowLoop As Long
    rowLoop = 1
    strValueToFind = InputBox("Enter a Search value in format xx.xx.xxxx")
    ' Loop row A to find value, number corrosponds to letter position in alphabet
    For rowLoop = 1 To Columns.Count
        If Cells(3, rowLoop).Value = strValueToFind Then ' If value is in C then do something
            ' start on cell found from date needed - look at copying range on same Column

            Sheets("Vessels").Range("C10:C13").Value = Sheets("Tidal").Range("D4:D7").Value
            Sheets("Vessels").Range("D10:D13").Value = Sheets("Tidal").Range("D9:D12").Value
            MsgBox ("Found value on col " & rowLoop) '
            Exit Sub
        End If
    Next rowLoop ' This is row number, do something with this

    ' This MsgBox will only show if the loop completes with no success
    MsgBox ("Date not found, make sure you have input the date correctly")

End Sub

The code looks for a date in a sheet, and copies data based on that cell. What I need is for this to become automatic.

The problem:
In C, or Python I would grab the 'cell found' as a value. I would then use that value inside the range to represent the column letter so that based on the 'cell found' it would copy the rows below that same column and paste into the new sheet on the same column, one cell down and paste in horrizontal instead of verticle.
In short, if date is on column C, copy the data below on that same column. Then paste into predetermined positions (C10:C13 and D10:D13 in this case) for displaying numerous fields of data on another sheet. Once I have the basic concept, the method only needs extrapolating to fill the function of the rest. Any idea's?

Simplified program is meant to be like this:
Input number.
Find column where data appears and define it as 'DATACELLFOUND'
Copy three cells, one cell below 'DATACELLFOUND' and paste into C10:C13
Copy three cells, starting from six cells below 'DATACELLFOUND' and paste into D10:D13.

Thank you in advance!

Cells(3, rowLoop).Offset(1,0).Resize(4).Value and Cells(3, rowLoop).Offset(6,0).Resize(4).Value is probably what you are looking for. — BrakNicku
Awesome, that worked perfectly for the cells below, thank you. You should make this an answer I think and explain it abit. Works perfectly for me. — Savagefool

Excel Excel · Accepted Answer · 2015-08-19T08:25:53

Try using this function to get the column in which data is found.

 Public Function columnletter(i As Integer)
If i > 26 Then
    columnletter = Chr(Int((i - 1) / 26) + 64) & Chr(((i - 1) Mod 26) + 65)
Else
    columnletter = Chr(i + 64)
End If
End Function

Excel VBA - Define value as cell position, copy below same cell position, paste

2 Answers