Amend Code

**Burt_100** · 04-20-2015, 08:19 AM

Hi,
Can anyone help to amend this code? I need the code to add a zero in columns B&C in range if I in sheet 2 is not available.

Sub Burt_100()



   
   Dim Sh1 As Worksheet
   Dim Sh2 As Worksheet
   Dim i As Integer
   Dim Col_B As String
   Dim Col_C As String
   Dim column As String
   Dim Lastrow As Integer
   Dim value As Integer
    
    
        Application.ScreenUpdating = False
    
        Set Sh1 = ThisWorkbook.Sheets(1)
        Set Sh2 = ThisWorkbook.Sheets(2)
        'activating sheets is not really necessary and it's really dangerous
        'to rely on the active sheet to define steps in your code...
        'i left the activate lines in here so you can see your stuff but
        'i added Sh1 or Sh2 in several places to help keep it all straight
        
           Sh1.Activate
         'your comments make it sound like .select somehow worked here but i
         'don't understand how that would be the case - i used .row to get it
         'to work for me
            Lastrow = Sh1.Range("B6").End(xlDown)(2, 1).Row
            
            
            Sh2.Activate
            For i = 1 To Sh2.Range("A:A").End(xlDown).Offset(1, 0).Row
                
                If Trim(Sh2.Cells(i, 1)) = "I" Then
                
                    Col_B = Sh2.Cells(i, 2)
                    Col_C = Sh2.Cells(i, 3)
                    
                   Sh1.Activate
            'again your comments make it sound like cells(Lastrow) was working
            'but i got an error every time i tried to run anything so i switched
            'these lines to cells(Lastrow, 2) and cells(Lastrow, 3)
                        Cells(Lastrow, 2) = Col_B
                        Cells(Lastrow, 3) = Col_C
                        
                        If Cells(Lastrow + 1, 2) = "" Then
                            Lastrow = Lastrow + 1
                        Else
                            Lastrow = Lastrow + 2
                        End If
                        
                        
                        Col_B = vbNullString
                        Col_C = vbNullString
                        
                End If
                    
           Next i
        
        Sh1.Activate
    Application.ScreenUpdating = True
    
End Sub

Thanks
Johnny

**LokeshKumar** · 04-20-2015, 08:51 AM

Upload the workbook........

**LokeshKumar** · 04-20-2015, 09:09 AM

Try this cod......

Sub Burt_100()



   
   Dim Sh1 As Worksheet
   Dim Sh2 As Worksheet
   Dim i As Integer
   Dim Col_B As String
   Dim Col_C As String
   Dim column As String
   Dim Lastrow As Integer
    
    
        Application.ScreenUpdating = False
    
        Set Sh1 = ThisWorkbook.Sheets(1)
        Set Sh2 = ThisWorkbook.Sheets(2)
        
        
           Sh1.Activate
        
            Lastrow = Sh1.Range("B7").Row
            
            
            For i = 1 To Sh2.Range("A:A").End(xlDown).Offset(1, 0).Row
                
                If Trim(Sh2.Cells(i, 1)) = "I" Then
                
                    Col_B = Sh2.Cells(i, 2)
                    Col_C = Sh2.Cells(i, 3)
                    
                   Sh1.Activate
            
                        Cells(Lastrow, 2) = Col_B
                        Cells(Lastrow, 3) = Col_C
                        
                        If Cells(Lastrow + 1, 2) = "" Then
                            Lastrow = Lastrow + 1
                        Else
                            Lastrow = Lastrow + 2
                        End If
                        
                        
                        Col_B = vbNullString
                        Col_C = vbNullString
                    
                Else
                        Cells(Lastrow, 2) = 0
                        Cells(Lastrow, 3) = 0
                        
                         If Cells(Lastrow + 1, 2) = "" Then
                            Lastrow = Lastrow + 1
                        Else
                            Lastrow = Lastrow + 2
                        End If
                End If
                    
           Next i
        
        Sh1.Activate
    Application.ScreenUpdating = True
    
End Sub

If your question is resolved, mark it SOLVED using the thread tools. Click on the star if you think some-1 helped you.

**Burt_100** · 04-21-2015, 05:04 AM

The code puts in 3 zero's for some reason even if I is available it still adds the contents in I + three zeros?

**LokeshKumar** · 04-21-2015, 05:18 AM

Hi
Just do me a favor I know this is the second time I am saying this.....
Run the code.......when it is putting 3 zero save the file and insert comment what you need and upload the file........

**Burt_100** · 04-21-2015, 06:15 AM

I have attached 3 examples for reference, Sample A is with the macro run with no data in sheet 2 with I in column A, Sample B is macro run with I occurring once in sheet 2 column A and sample C is macro run with I occurring twice in sheet 2 column A.Hope this helps and if you need anything further please let me know.

**LokeshKumar** · 04-21-2015, 06:55 AM

Earlier you requested......Correct me if I am wrong....

I need the code to add a zero in columns B&C in range if I in sheet 2 is not available.

Here on sheet 2 I is only available on 1st line and 5th line...

I 15 132 170.09
R 7692.22 142.32 176.68
T 2926.73 67.23 44.98
W 4920.96 111.22 133.71
I 16 220.61 170.09
R 12456 142.32 176.68
T 2926.73 67.23 44.98
W 4920.96 111.22 133.71

So output will only show you data in 1st line and 5th line

15 132
0 0
0 0
0 0
0 0
16 220.61
0 0
0 0
0 0
0 0

**Burt_100** · 04-21-2015, 07:35 AM

Sorry, communication error, On review "I" will only occur once at any one time in sheet 2 and if "I" in not available I want a Zero placed in range instead of what is input from the data which is stored in "I", I should have been a bit clearer sorry.

**LokeshKumar** · 04-21-2015, 07:54 AM

It means after one time if "I" appear then it should place zero like this.......

15 132
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0

**Burt_100** · 04-21-2015, 08:37 AM

No it means that if I does not appear it places a 0 instead

**LokeshKumar** · 04-23-2015, 05:58 AM

Originally Posted by Burt_100

No it means that if I does not appear it places a 0 instead

The Initial code I have pasted is doing the same job........if It is finding "I" in sheet 2 it is copying the data from next 2 cells and pasting into last available row in Sheet 1....If there is no "I" then it is going to put 0....

**LokeshKumar** · 04-21-2015, 09:17 AM

Hi,
Just check if it works for you........

Option Explicit
Sub Burt_100()

 
   Dim Sh1 As Worksheet
   Dim Sh2 As Worksheet
   Dim i As Integer
   Dim Col_B As String
   Dim Col_C As String
   Dim column As String
   Dim Lastrow As Integer
    
    
        Application.ScreenUpdating = False
    
        Set Sh1 = ThisWorkbook.Sheets(1)
        Set Sh2 = ThisWorkbook.Sheets(2)
        
        
           Sh1.Activate
        
            Lastrow = Sh1.Range("B7").Row
            
            
            For i = 1 To Sh2.Range("A:A").End(xlDown).Offset(1, 0).Row
                
                If Trim(Sh2.Cells(i, 1)) <> "I" Then
                
                    Col_B = Sh2.Cells(i, 2)
                    Col_C = Sh2.Cells(i, 3)
                    
                   Sh1.Activate
            
                        Cells(Lastrow, 2) = Col_B
                        Cells(Lastrow, 3) = Col_C
                        
                        If Cells(Lastrow + 1, 2) = "" Then
                            Lastrow = Lastrow + 1
                        Else
                            Lastrow = Lastrow + 2
                        End If
                        
                        
                        Col_B = vbNullString
                        Col_C = vbNullString
                    
                Else
                        Cells(Lastrow, 2) = 0
                        Cells(Lastrow, 3) = 0
                        
                         If Cells(Lastrow + 1, 2) = "" Then
                            Lastrow = Lastrow + 1
                        Else
                            Lastrow = Lastrow + 2
                        End If
                End If
                    
           Next i
        
        Sh1.Activate
    Application.ScreenUpdating = True
    
End Sub

**Burt_100** · 04-21-2015, 09:54 AM

When the macro is run now with no I in column A Sheet 2 instead of a zero the code is inputting figures instead of one row of zero's in columns B&C within the row in range?

So basically what sheet 1 columns B&C should look like in next available row when I is not available is sheet 2 is;

Column A Column B Column C
21/04/2015 0 0

and if sheet 2 contains I with 15 in column B and 245 in column C then columns B&C in sheet 1 next available row should show;

Column A Column B Column C
21/04/2015 15 245

Amend Code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Amend Code

Re: Amend Code

Re: Amend Code

Re: Amend Code

Re: Amend Code

Re: Amend Code

Re: Amend Code

Re: Amend Code

Re: Amend Code

Re: Amend Code

Re: Amend Code

Re: Amend Code

Re: Amend Code

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Please can someone help me amend this piece of code

[SOLVED] Help require to amend the code

Amend Code help

Amend code

Amend the Code

Bookmarks

Bookmarks

Posting Permissions