Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA help with data

  Asked By: Arnold    Date: Oct 24    Category: MS Office    Views: 6309
  

I have data imported from a server source. The data is entered in
rows. Each row contains the same set of columns. I think there are
about twenty data being sent. Each cell in the row has a specific
formula.

What I need to do is check to see if the current row (or A?) has data
in it. If it does, go to the next row and copy the formula of the
previous (above) cell.

So basically I need the data to propogate downwards in the
spreadsheet. As it is now, the data is just re-written in the first
row as the server sends the data.

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Becky Baker     Answered On: Oct 24

How about posting the code you have - it will help  us to help you.

 
Answer #2    Answered By: Stacie Martin     Answered On: Oct 24


Here's my scenario: I have intermittent (maybe every second or
spontaneous)data entered  in Excel via an OPC server. The data  is
entered per row, so I may have 20 values entered along row  1. The
cell's formula  may look like
this:
=OPC|TagValues!servername.datavalue

I think my code now will only copy  the cells above and not the
formula. Nor will it check  for an empty cell.

Public Sub CopyDown()
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Range("A" & i).Value = "" Then
Range("A" & i - 1 & ":CB" & i - 1).Copy Destination:=Range
("A" & i)
End If
Next i
End Sub


I need it to check the A* cell  to see if empty, if it's not jump to
the next row while copying the formula of the cell above.

 
Answer #3    Answered By: Adali Fischer     Answered On: Oct 24

I added a description of what should be copied (the formula), and that
should do it, I think. Your code should check  for empty cells in Column
A just fine.

Public Sub CopyDown()
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Range("A" & i).Value = "" Then
Range("A" & i - 1 & ":CB" & i - 1).Formula.Copy Destination:=Range
("A" & i)
End If
Next i
End Sub

 
Answer #4    Answered By: Olga Kates     Answered On: Oct 24

I'm not seeing what this code is really supposed to be doing.

It finds the bottom non-empty A cell, then works down from row  1 to that
row. If the A cell  in any row is empty, it duplicates the information from
the previous row into it using copy/paste.

The sub is called CopyDown, but it doesn't seem to do that. For example, it
will never put anything into the first empty row after your current  bottom
row. All it seems to do is fill in empty rows between 1 and your current
bottom row.

This does not fit in with the fact that you seem to be saying that the
server always puts the information into row 1.

You also talk about copying cells above but not the formula. I don't know
what you mean by this. If the cell above contains a formula, then that
formula will get copied in the normal copy/paste way.

I think you need to describe in more detail what the code is supposed to do.

 
Answer #5    Answered By: Milind Mishra     Answered On: Oct 24

I'm new to
the VBA stuff, so I'm seeking help. This code was given in another
forum, but I thought it would kind of help  me show what I'm trying
to do.

What I'm trying to accomplish is have the spreadsheet completely
automated. data  can be sent at any time, so the macro will (should)
check to see if the current row  has data. If it does, move to the
next row and copy  the previos row's formula.

I guess this code is worthless for most part.


Public Sub CopyDown()
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Range("A" & i).Value = "" Then
Range("A" & i - 1 & ":CB" & i - 1).Formula.Copy Destination:=Range
("A" & i)
End If
Next i
End Sub

 
Answer #6    Answered By: Jamie Williams     Answered On: Oct 24

I'm not sure from your description exactly what should happen, but your
description sounds pretty much like what the code does.

What constitutes "the current  row"? You have a loop here that looks at all
rows down to the bottom filled row. I suppose each of those rows will be
current for a moment, but is that what you mean?

Where does the data  come in? What runs the macro?

You need to step through the scenario in detail saying what the data feeder
might do and then what the macro needs to do when it is run. Use an
example, not just words.

 
Didn't find what you were looking for? Find more on VBA help with data Or get search suggestion and latest updates.




Tagged: