Returning 6 column letters/number values into a variable

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
Hi, I have a date that spans 6 columns, I wish the user to enter his/her desired date and return each column into a variable to be used later on, any suggestions? would I need 6 variables or could I use a loop on 1 variable i.e. for 'initial varible to 6 do . ?? thanls a lot,
Sean.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
You could load the columns into a 1-dimensional array. Say your dates go from B1:G1 on Sheet1. In code you would do something like --

Code:
Sub MySub()
  Dim vDates() As Variant 'this will be the array
  Dim i As Integer
  
  vDates = WorksheetFunction.Transpose(Sheets("Sheet1").Range("B1:G1"))
  '...other stuff here

  'to use the values, grab them from the array
  For i=1 to UBound(vDates)
    SomeOtherVariable = vDates(i)
    'etc
  Next i

End Sub
Denis
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
Thanks for the quick reply, sounds like a good idea. This may seem very trivial to you but I can't undertand why the following code won't compile, any suggestions?...

Private Sub FindColumn()

Dim vDates() As Variant 'this will be the array
Dim i As Integer


vDates = Cells.Find(StockAdjustments_frm.TextBox_Date.Value, After:="A1", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Columns


'to use the values, grab them from the array
For i = 1 To UBound(vDates)

StockAdjustments.Sheet1.Range("A1:F1").Value = vDates(i)

Next i


End Sub
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
It looks like you are searching for one value only -- the date on the form. Is that correct? If so, will all of the dates be adjacent or will they be scattered over the worksheet?

If they are adjacent, it's easy to get teh column of the first occurrence and then load a series of values into the destination cells.

If they are scattered, you will need to do the search 6 times and load each result into the array -- or place directly on the worksheet.

Also, you can't use Columns in that statement. You need to use the singular, Column. (Columns is a collection, not a single object, and doesn't have the same properties. Check when you create the code. After pressing the period, Column will display as an option but I'm betting Columns does not).

If you provide the info above, I can get you going in the right direction.

Denis
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126

ADVERTISEMENT

Thanks Very Much, the date only occurs once within the workbook but each date is merged accross 6 columns, does this make sense? thanks again.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
This worked for me, change search terms and sheet names to suit
Code:
Sub FindMerged()
    Dim colX As Long
    
    'find the cell containing the date, and place column in a variable
    Cells.Find(What:=175, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    colX = ActiveCell.Column
    'place the values in the destination cells
    Sheets("Sheet1").Activate
    Range("A1:F1").Select
    ActiveCell.Value = colX
    Selection.DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, Step _
        :=1, Trend:=False
End Sub
Denis
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126

ADVERTISEMENT

I'm getting a "Object variable or With block variable not set" when trying to compile:

Code:
Sub FindMerged()
    Dim colX As Long
    
    'find the cell containing the date, and place column in a variable
    Cells.Find(What:=StockAdjustments_frm.TextBox_Date.Value, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    
    colX = ActiveCell.Column
    
    'place the values in the destination cells
    Dim Wk As Workbook
    
    Set Wk = Workbooks.Add
    Application.DisplayAlerts = False
    Wk.SaveAs Filename:="C:/StockAdjustments.xls"
    
    Wk.Sheets("Sheet1").Activate
    Range("A1:F1").Select
    ActiveCell.Value = colX
    Selection.DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, Step _
        :=1, Trend:=False
End Sub
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Try the following:
Code:
Sub FindMerged()
    Dim colX As Long
    Dim Wk As Workbook
    Dim dtMyDate As Date
    
    dtMyDate = StockAdjustmnts_frm.TextBox_Date.Value
   
    'find the cell containing the date, and place column in a variable
    Sheets("Sheet1").Activate 'select the sheet first -- change name to suit
    Cells.Find(What:=dtMyDate, _
        After:=ActiveCell, _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False).Activate
    colX = ActiveCell.Column
   
    'place the values in the destination cells
    Set Wk = Workbooks.Add
    Application.DisplayAlerts = False
    Wk.SaveAs Filename:="C:/StockAdjustments.xls"
   
    Wk.Sheets("Sheet1").Activate
    Range("A1:F1").Select
    ActiveCell.Value = colX
    Selection.DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, Step _
        :=1, Trend:=False
End Sub
Denis
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
It Works :biggrin: Thank you very much, you have been a fantastic help, thank you, if you lived in the UK I'd buy you a beer, cheers again.
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
Just one more thing (sorry about this), the standard 'if foundcell is nothing then msgbox "no data" ' doesn't ssem to work with this block of code as you cannot state set foundcell = , any ideas? .... If you enter a date that doesn't exist on the sheet an error is thrown up :confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,818
Messages
5,574,501
Members
412,599
Latest member
Schu94
Top