Got Stuck at the logic of copying from sheet 1 to sheet2 after getting range address of formulated serial nos in col A of sheet1

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
205
Hello
In sheet 1:
Cols : A to C are fixed
Header Row = 3 with Col A =Sr No: Col B :Main Prod code and Col C: Prod Description
in Col A Sr No are calculated with formula ie A4= =1 A8= =A4+1 and so on
With the below code I am able to get Range of Each Formulated Serial No. from sheet1
Also the file link is provided below
VBA Code:
Option Explicit

Private Sub UserForm_Initialize()
Load UserForm1
UserForm1.Show vbModeless
Call AddSheetCopyColsRangeSrNos
End Sub

Public Sub AddSheetCopyColsRangeSrNos()

Dim wks As Worksheet
Set wks = Worksheets("Sheet1")

  Dim Ray() As String
  Dim c As Range, LastA As Range
  Dim rws As Long, k As Long
  wks.Activate
  Set LastA = wks.Range("A" & Range("C" & Rows.Count).End(xlUp).Row)
  For Each c In wks.Range("A4", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlFormulas)
    rws = 1
    If IsEmpty(c.Offset(1).Value) And c.Address <> LastA.Address Then rws = rws + Range(c, LastA).SpecialCells(xlBlanks).Areas(1).Rows.Count
    k = k + 1
    ReDim Preserve Ray(1 To k)
    Ray(k) = c.Value & " " & c.Resize(rws, 3).Address(0, 0)
  Next c
     ComboBox1.List = Ray
     ComboBox1.Text = Ray(1)
End Sub

Ie in combobox1 dropdown list
1 A4:C7
2 A8:C10
3 A11:C12
4 A13:C13
5 A14:C23 and so on
In the range A14:C23 there are optional offers range for the products which are in range from B4 to B14 for which I would like to copy for after Each Prodcut with coding in sheet 2
I got stuck in logic for coding to copy after getting the range address with above code.
In Sheet1 Optional Offer are different for Each Product as you can see
In Sheet 2 I ve added two columns ie ST/OP and “Prod Code with OP/Offers” the reason to add two columns was to use Filter in future
So what I want is to copy the range of Optional Offer after each Formulated Serial No of Main Prod

Eg Copy Optional Offers of Prod 1 ie range FromB16:C22 From Sheet1 to sheet 2 in range D8 to E14
And in Sheet2
range B4: to B14 to have only values “Prod1”
Range c4:C7 to have only values “ST”
Range C8:C14 to have only values “OP”
Sheet 2 is displayed for what I desire
Basically there are 10 products and the above was shown for 1 product
PFA link to download
Thanks
SamD
106
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hello Anyone

What i need is to get the range of Optional Offer after Each Formulated Sr.No as per above code mentioned in post #1 i need to get the range address of Optional Offers
If get the range address of Optional offers after Each Formulated Sr.No Range then copy syntax would be manageable
Would request you to pl study the file Sheet 1 and Output as in sheet2
Or if you could give a ref of any other thread which would be as similar as mine would also appreciate.
Also i am open to any other idea of coding apart from above ie if my logic is incorrect to what i desire
Representing output as per sheet 2 manually is task taking.

Will really appreciate if some replies are initiated :)

SamD
107
 
Upvote 0
Guys do you want me to post new thread for the similar type of question.
Or guide me to make still better clarity for you to understand the thread.
Am really strugling to get the range address from word Optional offers uptil the first blank row after getting the range address of formlated sr.nos
file attached in #post1 of this thread

SamD
109
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

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
Back
Top