• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find a column based on a value in a header row and copy/paste to the adjacent cells

Z

Zaman

New Member
Hi all, I'm quite new to vba and trying to write a macro for a project which i've been assigned to. I have sheet1 with 39 rows and 4625 columns. Row 12 has a column header with incremental month, e.g 'Nov-26', 'Dec-26'. Now, I need a macro that expand the date till Dec-31 with incremental months starting from Jan-27. Every single cells (row 13 to 38) below these columns which contain formulas need to be copied in the newly created 60 columns as well. Furthermore, the date column span 'Jan-27 to Dec-26' are repeated numerous times throughout the whole worksheet with a blank cell between them.

For the highly confedinatial ground, I'm unable to upload/copy/e-mail any sort of materials from my pc, so i couldn't upload a sample workbook or snapshot of my sheet, sorry for that. Any sort of help will be highly appreciated. Thanks in advace.
 
Hi SirJB7,

No, i didn't try recording macros for this problem. I was thinking it's bit complicated for me but i'll try now. Thank you for your reply.

Regards! Zaman
 
Hi Yasser, I can't upload right now, no data goes out from my office computer. But i'll recreate a sample workbook once i'm at home, i'll upload afterwards. Thank you.
 
After learning from the built-in features/searching in the internet, I came up to this macro but it needs to be modified again. Any sort of help will he highly appreciated, and sorry again that i can't provide any workbook.

Code: 
Sub Testmacro()   Dim ws As Worksheet
  Dim aCell As Range, bCell As Range, lrow As Long
  Dim foundAt As String
  
'set the relevant worksheet
  Set ws = Sheets("data")
  lrow = ws.Cells.Find("*", after:=ws.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'find the first date cell in row 12  
Set aCell = ws.Rows(12).Find(What:="Dec-26", LookIn:=xlValues, LookAt _
  :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  False, SearchFormat:=False)
  
  If Not aCell Is Nothing Then
  Set bCell = aCell
  foundAt = aCell.Address
  
  Do
  Set aCell = ws.Rows(12).FindNext(after:=aCell)
  
  If Not aCell Is Nothing Then
  If aCell.Address = bCell.Address Then Exit Do
  foundAt = foundAt & ", " & aCell.Address
  Else
  Exit Do
  End If
  Loop
  End If

The following codes give me the correct result if i place aCell.column or bCell.column in place of foundAt, but it only finds the first 'dec-26' and expand it till 'dec-31'. 'foundAt' has multiple comma separated cells containing 'dec-26'. When i try to convert it to a range object, it fails to compile as i'm not doing it correctly. Could anyone please tell me what needs to be done here:

Code: 
'expand date till dec-31  
ws.Columns(foundAt + 1).Resize(, 60).Insert

'autofill all the contents/formulas to the newly created cells  
ws.Cells(12, foundAt).Resize(lrow - 3).AutoFill Destination:=ws.Cells(12, foundAt).Resize(lrow - 3, 61), Type:=xlFillDefault
  
End Sub

Thank you again for your help.
 
You must log in or register to reply here.
Back
Top