Copy/Paste based on matched value that will change every second or so?

[vbagivesmeaheadache]

I'm in over my head with VBA! Please help.

I have a model with two paths. It iterates column data through one of two models based on values (I successfully VBA'd this part: go me! It took me 12 hours: oh no!)
I have two rows at the top of an output sheet that contain the current output of each model. As the inputs change about every second, the outputs do as well. (about, because the data isn't so that every-other column goes to one model or another, three could go to model 1 and then 5 could go to model 2 and so forth).

What I want is VBA to repeatedly look at cells AO3 and AO4 (since they will be updating as the model runs), find the corresponding values in AO6-AO35, and paste the values of the output in the correct row. AO3 and AO4 will always have the correct segment ID number--those change as the outputs change.

Screenshot for clarity..
I've not attempted to write any VBA for this yet because I can't find anything that matches and iterates and pastes.

Does anyone have any advice or code to point me to?

 

[HaHoBe]

Hi,

• If changes in A03 and A04 are made by code, the event Worksheet_Change might be used to monitor any change (event should be restricted to fire only if a change in either of these cells is found)
• 
  

  paste the values of the output in the correct row.

  Overwrite data (which means keeping the information at the same positions) or append to the right?
• Which data from which cells should go where?
• What about the different number of cells for the two rows?
• What about the empty cells?

You should know what you want the code to do - so please share the information.

Holger

 

[vbagivesmeaheadache]

-Neither overwrite or append to the right, because there will be no data currently in the rows where i want the data to paste.
The segment IDs will populate as the model iterates, in cells AO3 and AO4. I want the data from segment ID, say, 6, to be pasted into the segment ID 6 row (11). For all values. There will be no duplicates.

The cells on top are already in exactly the right order I want them to be pasted. Blanks should also be pasted. Both rows should be considered to go to AV

 

[HaHoBe]

One more:

in your picture you showed the first 30 ID to be present starting in Row 6. Will all IDs be present or will this change throughout? And as a picture tells a story: are we talking about numbers for AO3 and AO4 as well as for the cells further down? Is the area for the target rows filled chronologicly (without gaps and sorted lowest to greatest)?

If all numbers are present it's easy to determine the target row to be ID-Number + 5 for a match. If the rows below are not numbered constantly it's a little more challenging.

Is it possible to have numbers in AO3 and AO4 that are not present in the list?

Cia,
Holger

 

[vbagivesmeaheadache]

The rows are numbered constantly. They will be present throughout. It is not possible to have AO3 and AO4 not present in the list.The segment ID column will always be chronological, just like it is how (sometimes it will be smaller than 30). The data may not be coming in in AO3 and AO4 in perfect order though.

 

[HaHoBe]

Hi there,

code to be put behind the Worksheet (either right-click on the tab sheet and choose View Code or switch to the component in the VBE):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range
'check for change in relevant cells
If Not Intersect(Target, Range("AO3:AO4")) Is Nothing Then
  'do not trigger event again by writing to worksheet
  Application.EnableEvents = False
  'loop through cells that have been changed
  For Each rngCell In Target
    'we only need information from the cells AO3 and AO4
    If Not Intersect(rngCell, Range("AO3:AO4")) Is Nothing Then
      'copy the values from Row 3 or 4 Range AP:AW to corresponding ID
      Range("AP" & 5 + rngCell.Value).Resize(1, 8).Value = _
          rngCell.Offset(, 1).Resize(1, 8).Value
    End If
  Next rngCell
  'switch events on again
  Application.EnableEvents = True
End If
End Sub

There is no check if data is present in the target Element ID, data will be transferred anyway.

And just for interest: isn't it possible to fulfill whatever calculations/checks/lookups are done in row 3 and 4 in VBA-Code and instead of writing IDs into AO3 and/or AO4 to directly transfer the information to the target Element ID?

Ciao,
Holger

 

[vbagivesmeaheadache]

Hmmm. It doesn't work. No data populates in AP:AW as the models run and AO3 and AO4 change.

 

[HaHoBe]

Hi,

please find my workbook as a sample here.

There is a button to start this procedure to put in some values into AO3 and AO4:

Sub TestValues()
With Sheet1.Range("AO3:AO4")
  .Value = WorksheetFunction.Transpose(Array(4, 7))
  .Value = WorksheetFunction.Transpose(Array(25, 1))
End With
End Sub

Sheet1 is the codename of the sheet.

Behind Sheet1 is this slightly modified code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range
'check for change in relevant cells
If Not Intersect(Target, Range("AO3:AO4")) Is Nothing Then
  'do not trigger event again by writing to worksheet
  Application.EnableEvents = False
  'loop through cells that have been changed
  For Each rngCell In Target
    'we only need information from the cells AO3 and AO4
    If Not Intersect(rngCell, Range("AO3:AO4")) Is Nothing Then
      'added to make sure there is a numeric value in the cell
      If IsNumeric(rngCell) And Len(rngCell) > 0 Then
        'copy the values from Row 3 or 4 Range AP:AW to corresponding ID
        Range("AP" & 5 + rngCell.Value).Resize(1, 8).Value = _
            rngCell.Offset(, 1).Resize(1, 8).Value
      End If
    End If
  Next rngCell
  'switch events on again
  Application.EnableEvents = True
End If
End Sub

I just put in some formulas to get different results for each ID.

Holger

 

[vbagivesmeaheadache]

Hmmm. It works in your sheet but nothing at all happens in mine, with the exact same VBA. Unfortunately the values in AO3:AO4 and their rows cannot be changed using VBA--they connect to external models that I'm not in control of and that are still being developed, so it's much simpler just to call up the cells that are currently outputs, knowing that may change.

I wonder if that's the issue.

 

[HaHoBe]

Hi there,

and how will these externonal models put their values into the cells in question? My idea from the opening post was that you get the information from some model but can influence on what is put into the cells.

Holger