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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,439
Messages
5,831,632
Members
430,079
Latest member
lucasabreueng

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top