VBA select cells between two values

R

rifton

New Member
Joined
Apr 25, 2018
Messages
6
Dear,

I am a new poster. I found lots of information here about many ways to program and I am working as of today on an import file.
What it means is that there is a file in which information is held and it needs to go in a specific format in another file to be imported in a CRM.


What I have as of today is this:

Code: 
Dim constFixWeek As String
Dim rngRange As Range
Dim strPlanning As String
 
Dim wrkRollout As Workbook
Set wrkRollout = Excel.ActiveWorkbook

Dim shtplanning As Worksheet
strPlanning = "Planning Materiel"
Set shtplanning = wrkRollout.Worksheets(strPlanning)

constFixWeek = "WEEK "

strSelectCell = constFixWeek & intWeek

Set rngRange = shtplanning.Cells.Find(What:=strSelectCell, _
                                            LookIn:=xlValues, _
                                            LookAt:=xlPart, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False)

If Not rngRange Is Nothing Then
        rngRange.Select
Else
        MsgBox "No match found."
End If

Now I'm stuck because I need to know how I can select a number of rows between 2 weeks.
it can be a variable amount of cells to be selected of course
WEEK 19
value to be selected
value to be selected
value to be selected
WEEK 20

<tbody>
</tbody>
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Hey Steve059L

Sure, here's a snapshot of my data

Branch number
Branch Name
screen
laptop
case
reader
cable
WEEK 15
1000022274
1000021969
1000020798
1000019625
1000007442
310061
BRANCH 1
15
310114
BRANCH 2
8
WEEK 16
310002
BRANCH 4
8
WEEK 17
310906
BRANCH 6
6
310031
BRANCH 7
7
WEEK 18
310023
BRANCH 9
14
3
3
3
3
310077
BRANCH 10
8
1
1
1
1
310103
BRANCH 11
14
2
2
2
2
WEEK 19
310000
BRANCH 13
19
19
19
310111
BRANCH 14
35
8
8
310017
BRANCH 15
6
2
2
310067
BRANCH 16
5
1
1
310024
BRANCH 17
6
4
4

<tbody>
</tbody>

At the end of this project I will need to export this data into a very specific format for the CMS I'm using to order a certain number of article numbers(row2,column3)

export example of week 19 done manually(will be saved in .csv afterwards)

Branch NumberBranch Namefixed valuefixed valuefixed valuefixed valuefixed valuefixed valuearticle numberQtyQtyfixed value
310000BRANCH 13TMINPRM93RQTATT3110000222741919WO
310000BRANCH 13TMINPRM93RQTATT3110000219691919WO
310000BRANCH 13TMINPRM93RQTATT3110000207981919WO
310000BRANCH 13TMINPRM93RQTATT31100001962500WO
310000BRANCH 13TMINPRM93RQTATT31100000744200WO

<tbody>
</tbody><colgroup><col><col><col span="5"><col span="18"><col><col span="4"></colgroup>

As you can see lots of rows need to be empty.

What I thus need it to do is
- find branch number
- create as many branch numbers as articles even 0 Qty values(I used transpose manually)
- do all this per selected week

don't hesitate if you have questions
 
Upvote 0
This is the full code I have so far:
Code: 
Public Sub creation_import_materiel()


Dim wrkRollout, wrkCSV As Workbook
Dim strFullWeekName As String
Dim Target As Range

strFullWeekName = strSelectCells()

'Selection
Dim constFixWeek As String
Dim rngRange As Range
Dim strPlanning As String
Dim strSelectCells As String



Dim wrkRollout As Workbook
Set wrkRollout = Excel.ActiveWorkbook


Dim shtplanning As Worksheet
strPlanning = "Planning Materiel"
Set shtplanning = wrkRollout.Worksheets(strPlanning)

constFixWeek = "WEEK "

strSelectCell = constFixWeek & intWeek 'function that asks the week number


Set rngRange = shtplanning.Cells.Find(What:=strSelectCell, _
                                            LookIn:=xlValues, _
                                            LookAt:=xlPart, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False)
                                        

If Not rngRange Is Nothing Then
          rngRange.Select
          ActiveCell.Offset(1, 0).Range("A1:A5").Select
          
Else
        MsgBox "No match found."
End If

End Sub
 
Upvote 0
rifton said:
Hey Steve059L

Sure, here's a snapshot of my data

Branch numberBranch Namescreenlaptopcasereadercable
WEEK 1510000222741000021969100002079810000196251000007442
310061BRANCH 115
310114BRANCH 28
WEEK 16
310002BRANCH 48
WEEK 17
310906BRANCH 66
310031BRANCH 77
WEEK 18
310023BRANCH 9143333
310077BRANCH 1081111
310103BRANCH 11142222
WEEK 19
310000BRANCH 13191919
310111BRANCH 143588
310017BRANCH 15622
310067BRANCH 16511
310024BRANCH 17644

<tbody>
</tbody>

At the end of this project I will need to export this data into a very specific format for the CMS I'm using to order a certain number of article numbers(row2,column3)

export example of week 19 done manually(will be saved in .csv afterwards)

Branch NumberBranch Namefixed valuefixed valuefixed valuefixed valuefixed valuefixed valuearticle numberQtyQtyfixed value
310000BRANCH 13TMINPRM93RQTATT3110000222741919WO
310000BRANCH 13TMINPRM93RQTATT3110000219691919WO
310000BRANCH 13TMINPRM93RQTATT3110000207981919WO
310000BRANCH 13TMINPRM93RQTATT31100001962500WO
310000BRANCH 13TMINPRM93RQTATT31100000744200WO

<tbody>
</tbody>

As you can see lots of rows need to be empty.

What I thus need it to do is
- find branch number
- create as many branch numbers as articles even 0 Qty values(I used transpose manually)
- do all this per selected week

don't hesitate if you have questions
Click to expand...

Where are you getting the extra information from column C onwards?

As far as I can tell, you're only copying Branch Number & Branch Name, is this correct?

Are you trying to only pull it in one week periods? E.G week 19 - 20, or would you want to extend it to week 19 - 26 etc.
 
Upvote 0
The first table is done automatically with formula's based on an export from the CRM.

For the 2nd table, I'm entering
-Branch Number,
-Branch Name comes from a VLOOKUP
-the fixed value fields, well, they never change but have to be imported to the CRM(special codes)

I transpose
- the article number from the 1st table(always 15 values so far)
- Qty
- Qty is simple =AC2 in this example

So the Macro will get the week number, take the first value under it and make 15lines according to this structure,
then continue to do so until it finds the next week and I'll probably make it ask (later on)if you want to stop or go on with following week.
 
Upvote 0
Anyone knows how I can proceed.

I tried with the following code to go row by row, but it just goes on to an infinite loop
Code: 
Do Until ActiveCell.Value2 = Left(ActiveCell.Value2, 1) = "W"
i = i + 1
rngRange.Offset(i, 0).Select
Debug.Print Left(strSelectCell, 1)

Loop
 
Upvote 0
My understanding is just that if you want to select the cells between "Week 19" & "Week 20".

Is this correct? If so, this would do it.

Code: 
Sub Test()

WeekStr = "Week "
BeginWeek = InputBox("Please enter your starting week number.") '' E.G. 19 = Week 19
EndWeek = InputBox("Please enter your ending week number.") '' E.G. 20 = Week 20


Set BeginRng = ActiveSheet.Cells.Find(What:=WeekStr & BeginWeek, _
                                        LookIn:=xlValues, _
                                        Lookat:=xlPart, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=False)
    
Set EndRng = ActiveSheet.Cells.Find(What:=WeekStr & EndWeek, _
                                        LookIn:=xlValues, _
                                        Lookat:=xlPart, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=False)


If BeginRng Is Nothing Then
    MsgBox "No match found." & vbNewLine & "Please enter a valid starting week number."
ElseIf EndRng Is Nothing Then
    MsgBox "No match found." & vbNewLine & "Please enter a valid ending week number."
Else
    ActiveSheet.Range(Cells(BeginRng.Row + 1, 1), Cells(EndRng.Row - 1, 1)).Select
End If


End Sub

This will select between week 19 and week 20 if you input 19 in the first inputbox and 20 in the second.

Week 19
Values
Values
Values
Values
Values
Week 20
Values
Values
Values
Week 21

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

Does this work for you?
 
Upvote 0
Great!
That works.

I've changed Endweek = Beginweek +1 as it's always week+1

Now I can continue my code with copy and paste fun(yeey) and rearranging the necessary data into a CSV.

Great many thanks.

It's really weird to say thanks Lewzerrrr:LOL:

 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question
Remove Duplicates from a cell/ Create a cell with unique values from a cell with duplicate Values.
Replies
1
Views
386
eiloken
E
S
  • Question
VBA query pasting data in with new columns rather than just pasting values into existing columns?
Replies
0
Views
393
SendHalp3
S
A
  • Question
VBA split file and password
Replies
0
Views
716
Alexy888
A
WildBurrow
  • Solved
Tweaking Finding Next Empty Row Based on Column other than 'A'
Replies
5
Views
459
Joe4
Joe4
G
  • Question
VBA. Save sheets as values in separate workbooks. The problem is, all data in original file is saved as metadata.
Replies
0
Views
654
Gloffo
G

Forum statistics

Threads
1,214,591
Messages
6,120,428
Members
448,961
Latest member
nzskater

Share this page

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