Previous Topic Next Topic

What cell is my data coming from?

Solved/Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Apr 5, 2015 at 12:18 AM
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Apr 8, 2015 at 11:59 AM
Oh dear - back again ... :^(

After you guys help again with VBA.

I'm never going to volunteer to try script-writing again! (at least not without one to one tuition in a formal training environment and a 20 year course behind me).

OK - I have this code (I have commented what I want where I need it) .... 

Sub FillMeIn()

x = 1

For Each cell In Range("A1:D14").SpecialCells(xlCellTypeBlanks)
cell.Value = Sheets("Source").Range("G7")

' Here I want to copy the contents of the cell to the left of the cell just written to and paste it to cell "H7" on the "Source" sheet.

If x = 1 Then Exit For

Next cell

End Sub


What this does is find all the empty cells in the range A1-D14, and then fills in an empty cell with a value from another sheet [cell "G7" on sheet "Source"] which is in the same workbook.

What I need is the contents of the cell to the left of the cell just written to [one of those in the range "A1:D14"] to be used to fill a cell on another sheet [cell "H7" on sheet "Source"].

I know the loop sort of cancels itself after one itteration due to the x value - this was designed for this question so I can see how the solution code works, and will let me add the solution easily to the project I have been unfortunate enough to take on.


I really do appreciate your help - I am learning a lot from this, but I think I'll stick to hardware fault diagnosis in future.


Brian.

2 responses

Answer 1 / 2
MaxStart Posts 339 Registration date Tuesday March 3, 2015 Status Moderator Last seen July 3, 2015 69
Apr 6, 2015 at 04:38 PM
you really need to explain more ...
and you have to put in mind that there is no cell to the left of the column A:A, since your range starts from A1 and ends on D14
as on how to determine the left cell simply implement this method 
Cells(ActiveCell.Row, ActiveCell.Column - 1)
1
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 150
Apr 7, 2015 at 04:26 PM
Thanks Max,

I did know about there are no cells to the left of column A but this is fully occupied with the data I need to copy - the first column with an empty cell is (should be) always in column B so fortunately the issue cannot arise - and if it does then it would highlight an error in the spreadsheet setup which must then be addressed manually. There would be an error handler built in so this will not be an issue ... I say that with lots more confidence than I actually have! (but even without an error handler the macro will stop with an error)

Perhaps Im being thick here, but I dont know how to make myself clearer on this. Basically I get a value from a sheet called "Source" from cell G7. The code I have then places it into the first blank cell on a different sheet (lets call it "Target" for this example) which is the active sheet - for this examples sake, lets assume the empty cell is C3. All this so far I have achieved with the script I put earlier.

Now the bit im stuck on is ..
I need to copy the data in the cell next to where I have just pasted (in this example sheet "Target" cell B3) and paste it into cell H7 on sheet "Source".

I believe I have come up with a chunky solution, but I'm sure it can be refined. I have incorporated your line and it seems to work on my test spreadsheet. Im not sure how scailable this is though - only the Gods know at this stage. 

Sub FillMeIn()
Sheets("Source").Select
x = 1

For Each cell In Range("A1:D14").SpecialCells(xlCellTypeBlanks)
cell.Value = Sheets("Source").Range("G7")

Cells(cell.Row, cell.Column - 1).Select

        Selection.Copy
        Sheets("Target").Select
        Range("G7").Select
        ActiveSheet.Paste
        Sheets("Source").Select

If x = 1 Then Exit For

Next cell

End Sub

Thanks for your help, but if you can think of a better way then please let me know. (I do know that it is possible to manipulate sheets without making tham active and then switching back again, but its late and I need that bed that is calling me from afar.

Thanks Again,

Brian.
0
Answer 2 / 2
MaxStart Posts 339 Registration date Tuesday March 3, 2015 Status Moderator Last seen July 3, 2015 69
Apr 7, 2015 at 07:38 PM
I have the solution for u !!!!
but you need to tweak it up to fit your project.
I assumed that there would be multiple empty cells in column B and the values copied to the source sheet from the left cell of the one written by the code ...... bla bla bla .... you'll understand the code here it is, if you need further explanation I'm glad to keep up !! 

Sub go()
      Dim i, j As Integer
         i = 1
         j = 0
    Do While ActiveSheet.Cells(i, 1).Value <> ""
        If ActiveSheet.Name = "source" Then
          Exit Do
        End If
        If ActiveSheet.Cells(i, 2).Value = "" Then
        ActiveSheet.Cells(i, 2).Value = Sheet1.Cells(7, 7).Value
        Sheet1.Cells(7 + j, 8).Value = ActiveSheet.Cells(i, 1).Value
        j = j + 1
    End If
    i = i + 1
    Loop
MsgBox ("List ended on cell A" & Cells(i, 1).Row)
End Sub


dreaming is just a waste of time.
1
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 150
Apr 8, 2015 at 11:59 AM
Superb!

Thanks Max.

A combination of both of your comments and melding it into the code I already have has done the job nicely and allowed me to rid myself of several lines of code just in that bit that was holding me back. I can also see where your code can replace lots of my coding in other areas of my script, so even more of my awful code can go!

Thank you so much for your help. (but I'm still not volunteering for any more coding jobs!).

Unfortunately the project is still not complete, but the end is in sight. Hopefully no more coding questions form me ... ever!

Thanks again.

Brian.
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
AMD data change update new data to DMI
Luis -
dwebb -

1 response
Counting cells containing only numbers
jutulu -
jutulu -

4 responses
Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response
Spreadsheet data
ankharm -
TrowaD -

9 responses