CODEWITHSUNDEEP
excelvba
Fizor
Fizor

Reputation: 1530

Setting cell range values based on header and preceding columns

I'm trying to set a range of cell values equal to their column headings and cell values in column A & B.

Below you will see an image with a green highlighted area, this will be my range I want to populate.

I have shown the CONCATENATE in the formula bar, because that is basically what I want to achieve using VBA.

enter image description here

I am well aware that people are going to tell me where is your code, what have you tried - and honestly, the code I have is rubbish and doesn't even come close to what I want (But I have put it below so you can have a laugh and see how lost I am, it does nothing! )... I honestly don't even know what to search in Google to get myself in the right direction here...

Sub Get_Keys()

  '  Dim Report As Excel.Workbook 'The deliverable
  '  Dim ReportSheet As Excel.Worksheet 'Deliverables worksheet
    
   ' Dim Extract As Excel.Workbook 'This workbook
    'Dim ExtractSheet As Excel.Worksheet 'This workbook sheet
    
    'Workbooks.Open "Test_Report.xlsm"
    
    Application.DisplayAlerts = False
    
    Dim Report As Workbook
    Dim Current As Workbook
    
    Set Current = ActiveWorkbook
    'Dont update the screen - makes it work faster
    Application.ScreenUpdating = False
    
    Dim rng As Range
    Dim Headers As Range
    
    Set Headers = Current.Worksheets("Sheet1").Range("G14:AB15")
    'Grab Some Data and Store it in a "Range" variable
    Set rng = Current.Worksheets("Sheet1").Range("G19:AB21")
      
     
    
    'Turn on screen updating again - makes Excel usable
    Application.ScreenUpdating = True
    
   
End Sub


Public Sub DoAThing()
    Set Current = ActiveWorkbook
    Dim rng As Range
    Set rng = Current.Worksheets("Sheet1").Range("C19:AB21")
    
    With rng.Columns
        .Find(what:="*", after:=.Cells(1, 1), LookIn:=xlValues).Activate
    End With
End Sub

Public Sub DoAnotherThing()
    Set Current = ActiveWorkbook
    Dim rng As Range
    Set rng = Current.Worksheets("Sheet1").Range("G14:AB15")
Dim cel As Range
For Each cel In rng.Cells
    With cel
        Debug.Print .Address & ":" & .Value
    End With
Next cel
    
End Sub

Any help would appreciated, even if it is just to articles that I can read which guide me in the right direction!

Thanks!

Upvotes: 0

Views: 294

Answers (1)

Fizor
Fizor

Reputation: 1530

Thanks to those who commented offering assistance, I think I manged to somewhat figure it out for now. Having a problem with merged cells, but that is tomorrows issue :) Will update again if I figure it out completely.

Below is the code I am using now and it seems to give me what I want.

Public Sub DoAThing()
    Set Current = ActiveWorkbook
    Dim rng As Range
    Dim mycell As Range
    Set rng = Current.Worksheets("Sheet1").Range("K19:AB21")
    
    For Each mycell In rng
        mycell.Value = Range("C17").Value & "/" & Cells(mycell.Row, 3).Value & "/" & Cells(mycell.Row, 5).Value & "/" & Cells(14, mycell.Column) & "/" & Cells(15, mycell.Column) 'Range(mycell.Column + "14").MergeArea.Cells(1, 1).Value
    Next mycell
     
     
End Sub

Upvotes: 1

Related Questions