Reduce Numbers Down To A Specific Value

  • Thread starter Thread starter Paul Black
  • Start date Start date
P

Paul Black

Good afternoon,

I have a sheet named “Deletion”, where the results from the Macro are
produced.
I have a sheet named “Results”, where all the data to be used is
stored.
In the sheet named “Deletion” and in cell “D3” I have a value, this
could be anything from 1 to 50.
In the sheet named “Deletion” and in cell “D4” I have a value, this
could also be anything from 1 to 50.
In the sheet named “Results” the data to be used is in cells
“E15:???”.

Now I would like to produce two sets of numbers from this data.
(1) Start with the number in the sheet named “Deletion” and in cell
“D4”, this could be the number 30 for example, so we have numbers 1 to
30 in memory, now I want to start at the bottom right hand corner of
the data in the sheet named “Results” and take that number out of the
numbers 1 to 30, then move to the next cell on the left and take that
number out of the list and then the next number on the left and so on
moving left and then up to the next row and to the right and working
left and up until I have the total numbers left that is the value I
have in the sheet named “Deletion” and in cell “D3”. If the value in
the sheet named “Deletion” and in cell “D3” was 8, then I will be left
with 8 numbers from 1 to 30. I would like these numbers to be output
in sheet named “Deletion” and starting in cell “B7” and continuing
down.
The data for this is in the sheet named “Results” and in cells
“E15:J??”.

(2) Exactly the same as above but I would like these numbers to be
output in sheet named “Deletion” and starting in cell “C7” and
continuing down.
The data for this is in the sheet named “Results” and in cells
“E15:K??”.
The code below does some of what I would like. I hope it gives a good
idea of what I am trying to do.

Sub Find_Specific()
Dim rng As Range
Dim Number As Long

With Worksheets("Deletion")
Number = .Range("D3").Value
Set rng = .Range("B2:B43") ' Don't want this in the new Macro
End With

With Worksheets("Results")
Set rng1 = .Range(.Range("J15"), .Cells(Rows.Count, 5).End(xlUp))
End With

i = rng1.Count
Do While Application.CountA(rng) > Number
Set cell = rng1(i)
rng(rng1(i).Value).ClearContents ' Don't want this in the new Macro
i = i - 1
Loop

With Worksheets("Deletion")
.Range("B2:B50").Copy Destination:=.Range("D2:D50") ' Don't want
this in the new Macro
.Range("D2:D50").SpecialCells(xlBlanks).Delete Shift:=xlShiftUp '
Don't want this in the new Macro
End With

End Sub

Thanks very much in advance.

Kind regards,
Paul
 
Good afternoon,

I have a sheet named “Deletion”, where the results from the Macro are
produced.
I have a sheet named “Results”, where all the data to be used is
stored.
In the sheet named “Deletion” and in cell “D3” I have a value, this
could be anything from 1 to 50.
In the sheet named “Deletion” and in cell “D4” I have a value, this
could also be anything from 1 to 50.
In the sheet named “Results” the data to be used is in cells
“E15:???”.

Now I would like to produce two sets of numbers from this data.
(1) Start with the number in the sheet named “Deletion” and in cell
“D4”, this could be the number 30 for example, so we have numbers 1 to
30 in memory, now I want to start at the bottom right hand corner of
the data in the sheet named “Results” and take that number out of the
numbers 1 to 30, then move to the next cell on the left and take that
number out of the list and then the next number on the left and so on
moving left and then up to the next row and to the right and working
left and up until I have the total numbers left that is the value I
have in the sheet named “Deletion” and in cell “D3”. If the valuein
the sheet named “Deletion” and in cell “D3” was 8, then I will beleft
with 8 numbers from 1 to 30. I would like these numbers to be output
in sheet named “Deletion” and starting in cell “B7” and continuing
down.
The data for this is in the sheet named “Results” and in cells
“E15:J??”.

(2) Exactly the same as above but I would like these numbers to be
output in sheet named “Deletion” and starting in cell “C7” and
continuing down.
The data for this is in the sheet named “Results” and in cells
“E15:K??”.
The code below does some of what I would like. I hope it gives a good
idea of what I am trying to do.

Sub Find_Specific()
Dim rng As Range
Dim Number As Long

With Worksheets("Deletion")
  Number = .Range("D3").Value
  Set rng = .Range("B2:B43") ' Don't want this in the new Macro
End With

With Worksheets("Results")
  Set rng1 = .Range(.Range("J15"), .Cells(Rows.Count, 5).End(xlUp))
End With

i = rng1.Count
Do While Application.CountA(rng) > Number
   Set cell = rng1(i)
   rng(rng1(i).Value).ClearContents ' Don't want this in the new Macro
   i = i - 1
Loop

With Worksheets("Deletion")
  .Range("B2:B50").Copy Destination:=.Range("D2:D50") ' Don't want
this in the new Macro
  .Range("D2:D50").SpecialCells(xlBlanks).Delete Shift:=xlShiftUp '
Don't want this in the new Macro
End With

End Sub

Thanks very much in advance.

Kind regards,
Paul
Click to expand...

Hard to tell from this what you want.

Send your file with a complete explanation and before/after examples
to dguillett1 @gmail.com
 
Back
Top