Posts by dunc1234

Re: worksheet code to copy the value of a formula result that constantly changes

hi again
i couldn't get the match to work but i think i have managed to get nearly a desired result with

"Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("D4")


    If Not Intersect(target, Range("d4")) Is Nothing Then
  ' option to  Call MyCode if required
    Range("a5") = "Match"
    End If
End Sub
Sub MyCode()
'more code here if required
End Sub
"

Although it picks up with the change of cell i think i can make it work for me, thanks though for your input, but i'm off to bed and will try and put this into practice tomorrow

Re: worksheet code to copy the value of a formula result that constantly changes

Quote from Grimes0332;768874

That code would not work because a cell changing as a result of a calculation does not raise the Change event. You could try the code in the Calculate Event, scanning the relevant cells for 'Match', or you could use a Circular reference in E4

=IF(AND(D4="match", E4=0), D4, E4)

if D4 = "Match" and E4 is 0 (it will be 0 iwhen you first edit/copy the formula) then take the value of D4, otherwise take the value of E4. You will have to Enable Iterative Calculations and set the Max iterations to 1 in the Options/Formulas screen.

many thanks for that, i'll give it a try but the "Enable Iterative Calculations and set the Max iterations to 1 in the Options/Formulas screen" is beyond my knowledge, but i'm sure searching this forum and google will answer that.
Thanks again for looking and providing your assistance

Re: worksheet code to copy the value of a formula result that constantly changes

just when i thought i'd found the answer. i failed miserably to get the code to work

Re: worksheet code to copy the value of a formula result that constantly changes

i think i have found the answer here http://www.vbaexpress.com/kb/getarticle.php?kb_id=530 but if there is a more simple way i'm open to answers

Hi gurus

i'm stumped on this and i'll try to explain what i'm trying to achieve, but have uploaded a simplified version as well if my explanation is poor.

I capture external data in to Range A1:A50 of numbers , this data changes every minute or so, but sometimes a lot quicker

In Range B1:B50 is a formula crunching numbers in Range A1:A50.

In range C1:C50 is a formula to get a match result if reaching a criteria e.g If B6 value is >5 and < 10 "match"

The problem is as the values in column A6 changes the word "match" comes and goes in column C6

So in column D6 i want to paste the value "match" if it appears in column B so it stays in place in D6 whether it stays in C6 or not.

I gather it will be a trigger event in worksheet code rather than a macro, but from here i'm stuck.

Appreciate any help

Thanks in advance

dunc

Re: sum groups of data in a column

You are a marvel, works perfectly. My relief is immense as this code will be sorting a column with over 3000 cells. Thank you very very much

Hi thanks to all that help us (not so bright) that struggle with code, thought this would be fairly simple but i'm stuck again

in column A i have numbers that go concurrently up in value for an unspecified amount of cells, then drop to a low number and start again.
i would like to sum each group in column B.

For example the numbers in column A are 1,2,3,6,8,12,1,5,6,8,10,15,19,3,5,7,9,1,4,5 etc
i was hoping to get something along the line of
Select A2 - start of data
progress down column until cell value is less the cell above
select range and sum into column B
reset and continue down column.

so in the example data above, in cell B7 = 32 ,a sum of A2:A7 (1,2,3,6,8,12) and cell B14= 64, a sum of A8:A14 (1,5,6,8,10,15,19) etc

As always many thanks to all those that help

Re: copy and paste with two criteria

Quote from shknbk2;765532

Try this code:

Code

Sub copyCells()
    Dim i As Integer, j As Integer, sAddr As String, fAddr As String
    
    Application.ScreenUpdating = False
    Range("B1").Select
    sAddr = ActiveCell.Address
    On Error Resume Next
    Columns("B:B").Find(What:="X", After:=Range(sAddr), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
    Do
        If ActiveCell.Address <> sAddr Then
            sAddr = ActiveCell.Address
            If fAddr = "" Then
                fAddr = ActiveCell.Address
            End If
            i = ActiveCell.Row
            j = i
            Do While Cells(j, 1).Value < Cells(j + 1, 1).Value
                j = j + 1
            Loop
            Range(Cells(i, 1), Cells(j, 1)).Copy Range(Cells(i, 3), Cells(j, 3))
        Else
            Exit Do
        End If
        On Error Resume Next
        Columns("B:B").Find(What:="X", After:=Range(sAddr), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        On Error GoTo 0
    Loop While ActiveCell.Address <> fAddr
    Application.ScreenUpdating = True
End Sub

Display More

Gosh! thankyou so much, works a treat and far beyond my capabilities, you are a guru

i think i'll have to admit to being dim as gone round in circles on this (probably simple) problem

in column A i have data such as 1,3,7,9,12, 5,6,18,23, 4,5,6,11,13,15,18,20 etc. basically numbers going up in value for varying amounts of times and then dropping back down to a low number and starting again to go up in value. (i have left spaces in example to show where the groups start at a low number and increasing in value)

in column B i have the value X next to the lowest number on some of these groups of numbers.

When this happens i want to copy the range of numbers from A to column C

Pseudo code would be;
Select B2
scroll down until X is found
offset 1 cell to left to column A
select range of cells down until value of cell is smaller
copy that range to column C

Return to column B and scroll down to next occurrence of X
then loop;

so if for example data starts in A2 and there was an X in B2 it would copy the range A2:A6 (having the values1,3,7,9,12) to Column C
Then continue down column B until it finds another X.

I've got in all sorts of muddle with offset and loops and appreciate any help

Re: find repeating number sequence in a column

Hi

I can honestly say that your are a genius, the code works exazctly how i need it first time. i am over the moon and you have made my day.

Thanks you

Quote from nilem;760295

Hi dunc1234,
try this

Code

Sub ertert()
Dim x, y(), i&, j&, s$, k&
k = Application.InputBox("Enter 3 or 4 or 5", Default:=3, Type:=1)
If k = 0 Then Exit Sub
x = Range("A1", Cells(Rows.Count, 1).End(xlUp)).Value
ReDim y(1 To UBound(x), 1 To 1)


With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(x, 1) - k - 1
        For j = i To i + k - 1
            s = s & x(j, 1)
        Next j
        If .exists(s) Then
            For j = .Item(s) To .Item(s) + k - 1
                y(j, 1) = "match"
            Next j
            For j = i To i + k - 1
                y(j, 1) = "match"
            Next j
        Else
            .Item(s) = i
        End If
        s = vbNullString
    Next i
End With


Range("B1").Resize(UBound(y)).Value = y
End Sub

Display More

hi guru's

I have a column of numbers from 1-7 in a random order and am trying to find repeat sequences of three or more numbers.
For example 1 5 5 4 3 2 3 4 2 1 5 7 6 1 4 2 1 7 7 4 3 1 2 5 5 4 etc etc there are over a 10000 numbers in the column.

In the above the is the sequence "421" repeated twice and also "521"

by the side of these "found" sequences i would like the word "match" placed in column B as the number list is in column A

Although i have used three number sequences in the example, i would also like the ability to match four and five number sequences if possible.

I have attached an example file as well.

Thanks in advanced to anyone who can help

Re: marking repeat numbers in blocks of numbers in adjacent column

sorry for late response, still had difficulties with first solution but got it working eventually, however many thanks to SO for his solution that worked great. appreciate every ones time who looked at this to help sort it.

thanks

Re: marking repeat numbers in blocks of numbers in adjacent column

further info, it came up with error on 1st line

Sub nDup(0

tried placing bracket on the end

Sub nDup(0)

which didn't work

changed it to

Sub nDup()

which i presume is correct but still didn't work,

sorry

Re: marking repeat numbers in blocks of numbers in adjacent column

hi

thanks for help but tried it and sadly doesn't do anything that i can see, i'm on office 2013 if that makes any difference.

apologies for long post but trying to attach a a spreadsheet failed, i'm trying to mark with an x repeating numbers
the first batch is a small part of the existing, the second part shows how i wish it to look.

This is in pseudo code
from top going downwards

start at B1
go down until value of cell is X
go across to column A
If cell below is = to active cell, place X in column B
if next cell below is = to above cell, place x in column B
if next cell down <> to one above, go to Column B, scroll down to next occurrence of X

so if the cells in column A have the same number, put an x in column b
if not, move down to next occurence of x

[TABLE="width: 128"]

[TD="width: 64, align: right"]830[/TD]
[TD="width: 64"]x[/TD]

[TD="align: right"]830[/TD]

[TD="align: right"]820[/TD]

[TD="align: right"]805[/TD]

[TD="align: right"]790[/TD]

[TD="align: right"]790[/TD]

[TD="align: right"]790[/TD]

[TD="align: right"]790[/TD]

[TD="align: right"]780[/TD]

[TD="align: right"]780[/TD]

[TD="align: right"]780[/TD]

[TD="align: right"]765[/TD]

[TD="align: right"]765[/TD]

[TD="align: right"]745[/TD]

[TD="align: right"]720[/TD]

[TD="align: right"]830[/TD]

x

[TD="align: right"]830[/TD]

[TD="align: right"]830[/TD]

[TD="align: right"]830[/TD]

[TD="align: right"]830[/TD]

[TD="align: right"]830[/TD]

[TD="align: right"]820[/TD]

[TD="align: right"]820[/TD]

[TD="align: right"]820[/TD]

[TD="align: right"]820[/TD]

[TD="align: right"]815[/TD]

[TD="align: right"]805[/TD]

[TD="align: right"]795[/TD]

[TD="align: right"]785[/TD]

[TD="align: right"]785[/TD]

[TD="align: right"]780[/TD]

[TD="align: right"]795[/TD]

x

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]745[/TD]

[TD="align: right"]745[/TD]

[TD="align: right"]745[/TD]

[TD="align: right"]745[/TD]

[/TABLE]

how i want it to look

[TABLE="width: 128"]

[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[TD="align: right"]830[/TD]

x

[TD="align: right"]830[/TD]

x

[TD="align: right"]820[/TD]

[TD="align: right"]805[/TD]

[TD="align: right"]790[/TD]

[TD="align: right"]790[/TD]

[TD="align: right"]790[/TD]

[TD="align: right"]790[/TD]

[TD="align: right"]780[/TD]

[TD="align: right"]780[/TD]

[TD="align: right"]780[/TD]

[TD="align: right"]765[/TD]

[TD="align: right"]765[/TD]

[TD="align: right"]745[/TD]

[TD="align: right"]720[/TD]

[TD="align: right"]830[/TD]

x

[TD="align: right"]830[/TD]

x

[TD="align: right"]830[/TD]

x

[TD="align: right"]830[/TD]

x

[TD="align: right"]830[/TD]

x

[TD="align: right"]830[/TD]

x

[TD="align: right"]820[/TD]

[TD="align: right"]820[/TD]

[TD="align: right"]820[/TD]

[TD="align: right"]820[/TD]

[TD="align: right"]815[/TD]

[TD="align: right"]805[/TD]

[TD="align: right"]795[/TD]

[TD="align: right"]785[/TD]

[TD="align: right"]785[/TD]

[TD="align: right"]780[/TD]

[TD="align: right"]795[/TD]

x

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]760[/TD]

[TD="align: right"]745[/TD]

[TD="align: right"]745[/TD]

[TD="align: right"]745[/TD]

[TD="align: right"]745[/TD]

[/TABLE]

Re: couple data from cell in column A of random to Column C dependent on data in colu

Thankyou so much Stephen R

a bit more complicated than i thought so greatly appreciated and works a treat,

appreciate the other comment of sample sheet but to be fair i thought i'd explained it reasonable well.

Hi

i have a few thousand lines of data of numbers in column A

in column B at varying intervals is the letter X, the gap may vary between 2 and 50

eg X at B7, B52, B81, B100 etc, with no data between.

Starting at the bottom of the data and working upwards i am trying to achieve;

When X is in column B, go to column A and going upwards find the first occurence of a number under the value of 6.

When that value is found, copy it to the same row in column C

Offset left to column B

Continue upwards (thus ignoring the rest of the numbers) until the next occurence of X in column B and repeat the loop.

Pseudo code of what i'm trying to do if this explains better

Go to end of column A
offset one cell right to column B
Go upwards until cell data = X
offset one cell left to column A
If data is less than 5, copy data to column C
If above 5 go up one cell
(continue until first occurence of number under 5)
(when found, copy to column C)

Offset to Column B and go upwards to next occurence of X

repeat above sequence until reaching cell A1

Probably fairly simple to a guru but i'm really stuck, thanks in advance for any help.

Re: sorting continuos blocks of data in column A

my mistake, all working perfectly

many many thanks

Re: sorting continuos blocks of data in column A

hi, sadly error saying cells not found on line Set rng = Range("A:A").SpecialCells(xlCellTypeConstants, 1)

hi

i have several 1000 lines of blocks of data containg numerical data separated by a space but each block is headed by the same title for example

Cell Content
A1 salesNumbers
A2 5
A3 8.5
A4 2
A5 9
A6 Empty Row
A7 SalesNumbers
A8 55
A9 45
A10 66
A11 Empty Row
A12 Salesnumbers

etc etc so when sorted A2=2 A3=5 A4=8.5 A5=9
A8 = 45 A9 = 55 A10 = 66

The title is always the same and not important to keep

there is data to the sides of column A (in B to G) that i want sorting along with column A

I want to sort each block of numbers and then move onto the next block (as shown above)

keeping the empty row is not important.

pseudo code
a1 select
select range to empty row
sort data
move down
select next range to next empty row
sort data

stop when more than 3 empty rows detected (2 would be ok but 3 for safety incase extra row sneaked in)

my pseudo code is a guess on what the coding might be , i've combed the sites and cannot find a solution anywhere and this is doing me in , hopefully simple to a guru

many thanks in advance