VBA codes for skipping cells

[njls]

I am new to the vba codes and this is my first post. i am trying to copy cell A1,A2,A3,A4 from Book1 and on Book 2, find a todays date on column A:A then paste on found date row but column B,C,F,G. On D,E columns i have formulas. curretly i have the bottom codes but i don't know how to skip cells. Any help will be appreciated.

Sub Button1_Click()
Dim wbk As Workbook
strFirstFile = "book1"
strSecondFile = "book2"
Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Proj_Total_Delq-20120622 RESI P")
Range("B2,B3").Copy
End With
Set wbk = Workbooks.Open(strSecondFile)
With wbk.Sheets("RESI (nonAltA)")
Dim FoundDate As Range
Set FoundDate = Worksheets("RESI (nonAltA)").Columns("A:A").Find(DateValue(Now), LookIn:=xlValues, lookat:=xlWhole)
If Not FoundDate Is Nothing Then ' if we don't find the date, simply skip.
FoundDate.Offset(1, 0)(0, 2).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False ' You can see that the first argument in PasteSpecial is set
Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Proj_Total_Delq-20120622 RESI P")
End With
End If
End With
End Sub

[Zack Barresse]

Hi there, welcome to the board!

I would add a few more checks and balances to your code. Most importantly is checking if the file is already open or not, which can cause problems if you are trying to open a file that's already open. Take a look at this code.

Sub Button1_Click()

Dim WBK1 As Workbook
Dim WBK2 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim FoundDate As Range
Dim bWasFileOpen As Boolean
Dim strFirstFile As String
Dim strSecondFile As String
Dim sFirstPath As String
Dim sSecondPath As String
Dim aValues(1 To 4) As Variant

'/// File variables
strFirstFile = "book1"
sFirstPath = "C:\PathGoesHere\"

strSecondFile = "book2"
sSecondPath = "C:\PathGoesHere\"

Call TOGGLEEVENTS(False)

'/// First file
If ISWBOPEN(strFirstFile) = True Then
Set WBK1 = Workbooks(strFirstFile)
bWasFileOpen = True
Else
Set WBK1 = Workbooks.Open(strFirstFile)
bWasFileOpen = False
End If

'/// Second file
If ISWBOPEN(strSecondFile) = True Then
Set WBK2 = Workbooks(strSecondFile)
bWasFileOpen = True
Else
Set WBK2 = Workbooks.Open(strSecondFile)
bWasFileOpen = False
End If

'/// Set worksheets
Set WS1 = WBK1.Sheets("Proj_Total_Delq-20120622 RESI P")
Set WS2 = WBK2.Sheets("RESI (nonAltA)")

'/// Get values
aValues(1) = WS1.Range("A1").Value
aValues(2) = WS1.Range("A2").Value
aValues(3) = WS1.Range("A3").Value
aValues(4) = WS1.Range("A4").Value

'/// Find date
Set FoundDate = WS2.Columns("A:A").Find(DateValue(Now), LookIn:=xlValues, lookat:=xlWhole)
If Not FoundDate Is Nothing Then ' if we don't find the date, simply skip.

'/// Set values
FoundDate.Offset(0, 1).Value = aValues(1)
FoundDate.Offset(0, 2).Value = aValues(2)
FoundDate.Offset(0, 5).Value = aValues(3)
FoundDate.Offset(0, 6).Value = aValues(4)

End If

Call TOGGLEEVENTS(True)

End Sub

HTH

[njls]

[njls]

- Thanks Zack, But i am getting following error.
- Compile error:
- Sub or Function not defined
-
- Also, I want to add something else. In book1, if column A:A has a 1-29 Days then copy cell B,C and then on Book 2, Column A:A find a today date and pate on cell C,D. if in book 1 column A:A dosent have 1-29 days then paste 0 in column C,D. Can i do this? Thanks in Advance!
-
- Note: So in Book1 i have to add 1-29 days, 29-30 days, 30-60 days on column A:A and so on. and sometime we don't have one of the bucket so i want to paste it on book2 which has all buckets and they don't change. leave 0 in buckets on book 2 if they don't find that bucket in book1.

[Aussiebear]

Have you commented out or deleted your initial code?

[Teeroy]

Hi njls,

Zack's right about the need for checks and balances but TOGGLEEVENTS and ISWBOPEN appear to be a sub and function respectively that Zack has written and uses rather than part of excel. From the context I can guess what they do but it may be easier to ask Zack to copy them here for you.

Also I think you'll need to change
Set WBK1 = Workbooks.Open(strFirstFile)

to
Set WBK1 = Workbooks.Open(sFirstPath & strFirstFile)

and similar for the second workbook.

[Zack Barresse]

@Teeroy: Yes, good catch! Thank you! Goof on my part. Apologies. And yes, those two routines are separate, as they were designed to be. The TOGGLEEVENTS() was designed to put at the start and end of code as a simple way to help optimize the code. Since I use it in multiple routines, it doesn't make sense for me to keep typing the same lines of code over and over, so I put it in a routine and write one line to turn things off, then one line to turn things back on. The other function is a test to see if the workbook is open or not. I apologize for not posting them, I thought I included them!

@njls: Where does the error occur? What line is highlighted if you hit Debug? I'm suspecting it's from what Teeroy is talking about, which I really apologize for.

Here is the code with the two routines...
Option Explicit

Sub Button1_Click()

Dim WBK1 As Workbook
Dim WBK2 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim FoundDate As Range
Dim bWasFileOpen As Boolean
Dim strFirstFile As String
Dim strSecondFile As String
Dim sFirstPath As String
Dim sSecondPath As String
Dim aValues(1 To 4) As Variant

'/// File variables
strFirstFile = "book1"
sFirstPath = "C:\PathGoesHere\"

strSecondFile = "book2"
sSecondPath = "C:\PathGoesHere\"

Call TOGGLEEVENTS(False)

'/// First file
If ISWBOPEN(strFirstFile) = True Then
Set WBK1 = Workbooks(strFirstFile)
bWasFileOpen = True
Else
Set WBK1 = Workbooks.Open(sFirstPath & strFirstFile)
bWasFileOpen = False
End If

'/// Second file
If ISWBOPEN(strSecondFile) = True Then
Set WBK2 = Workbooks(strSecondFile)
bWasFileOpen = True
Else
Set WBK2 = Workbooks.Open(sSecondPath & strSecondFile)
bWasFileOpen = False
End If

'/// Set worksheets
Set WS1 = WBK1.Sheets("Proj_Total_Delq-20120622 RESI P")
Set WS2 = WBK2.Sheets("RESI (nonAltA)")

'/// Get values
aValues(1) = WS1.Range("A1").Value
aValues(2) = WS1.Range("A2").Value
aValues(3) = WS1.Range("A3").Value
aValues(4) = WS1.Range("A4").Value

'/// Find date
Set FoundDate = WS2.Columns("A:A").Find(DateValue(Now), LookIn:=xlValues, lookat:=xlWhole)
If Not FoundDate Is Nothing Then ' if we don't find the date, simply skip.

'/// Set values
FoundDate.Offset(0, 1).Value = aValues(1)
FoundDate.Offset(0, 2).Value = aValues(2)
FoundDate.Offset(0, 5).Value = aValues(3)
FoundDate.Offset(0, 6).Value = aValues(4)

End If

Call TOGGLEEVENTS(True)

End Sub

Public Sub TOGGLEEVENTS(blnState As Boolean)
Application.DisplayAlerts = blnState
Application.EnableEvents = blnState
Application.ScreenUpdating = blnState
If blnState Then Application.CutCopyMode = False
If blnState Then Application.StatusBar = False
End Sub

Public Function ISWBOPEN(wkbName As String) As Boolean
On Error Resume Next
ISWBOPEN = CBool(Workbooks(wkbName).Name <> "")
On Error GoTo 0
End Function

I'm not sure what your other request means. Is there any way you could provide us with a detailed example? Most importantly, please describe - in as much detail as possible (or upload a sample file) - your data structure.