Telling VBA to find a cell, row, column, range

[opie_18]

Hello,

Main question I have is: Am I calling/referring to the rows/columns poperly... i say I'm not because no values get exported for my iDate, nor my Rev.

I have row B starting at B11:B41, those have dates i need to pull. Corresponding to those rows in column B is Columns J-V stepping 2 to pull the Rev.

Ex.

B11 = 1/1/2004 (if B11 is date then idate = B11) and
the Rev = J11, L11, N11, P11, R11, T11, V11...

I go through this all the way down to cell B41.

I just don't think I am Declaring them right i.e string, interger etc... Get Type Mismatch errors.
nor refrecing the cells in the rows properly...

Thanks,

John




1. Dim sCol As Integer

2. Set rAccts1 = oMarketSheet.Columns("B").SpecialCells(xlCellTypeConstants)
3. Set rRow = oMarketSheet.Rows.Range("B11:B41")

4. ' For Each rCell In Range(Range("B11"), Range("B11").End(xlDown)).Cells
5. For Each rRow In rAccts1

6. If IsDate(rCell.Value) Then 'Cells(11, 2)
7. For sCol = oMarketSheet.Columns("J").rRow.Value To oMarketSheet.Columns("V").rRow.Value Step 2 '
8. iDate = rAccts1.rRow.Value

9. Select Case Accnt
10. Case sCol = oMarketSheet.Columns("J").Value 'Cells(rCell, i) = j
11. Accnt = "010-41200"
12. Case sCol = oMarketSheet.Columns("L").Value 'Cells(rCell, i) = l
13. Accnt = "010-41215"
14. Case sCol = oMarketSheet.Columns("N").Value 'Cells(rCell, i) = n
15. Accnt = "010-41225"
16. Case sCol = oMarketSheet.Columns("P").Value 'Cells(rCell, i) = p
17. Accnt = "010-41340"
18. Case sCol = oMarketSheet.Columns("R").Value 'Cells(rCell, i) = r
19. Accnt = "010-41230"
20. Case sCol = oMarketSheet.Columns("T").Value 'Cells(rCell, i) = t
21. Accnt = "010-41290"
22. Case sCol = oMarketSheet.Columns("V").Value 'Cells(rCell, i) = v
23. Accnt = "010-41240"
'Case Else Accnt = "010-41320"
24. End Select
25. 'Rooms = Cells(rCell.Value, i)
26. Rev = oMarketSheet.Columns("J" + 1).rRow.Value

[RobDog888]

What line(s) are you getting the type mismatch error(s)?

Ps, you can use the [vbcode]insert your code here[/vbcode]
tags to format your code so it looks like it does in the VB IDE.

[opie_18]

Oh, I didn't know that... thanks...

Type mismatch is in row 2, 7,10...
Also row 2,3,7,8 when I dubug and move mouse over them the values show "Nothing" or ""

Thanks,

John

[RobDog888]

Ok, where are the variables defined?

It looks like rRow needs to be dimmed as a Range since you are setting it to a range.

[opie_18]

I am trying to hard code it in a little... however I am having problems with my select case down there in the code... I need that 10 to be static... cause i have Columns 10, 12, 14,... 22 to test against. should the 10 go on the other side of the "=" or how should I refer to it...

2ndly is "oMarketSheet.Columns(rCount, ColAccntE)" syntaxed properly?

Thanks much...

John

Dim rCount As Integer
  Dim ColDate As Integer
  Dim ColAccntB As Integer
  Dim ColAccntE As Integer
  Dim ColRev As Integer
    
  rCount = 11
  ColDate = 2
  ColAccntB = 10
  ColAccntE = 22
  ColRev = 11
    
'For oMarketSheet.Rows(rCount,) To oMarketSheet.Columns(ColDate)
Do While rCount <= 42
 
     If IsDate(oMarketSheet.Cells(rCount, ColDate)) Then
          Do While ColAccntE < 23  'sCol = oMarketSheet.Rows(rCount, ColAcccntB)  oMarketSheet.Columns(rCount, ColAccntE) ' ColAccnt)
          iDate = oMarketSheet.Cells(rCount, ColDate)
                 Select Case Accnt
                        Case oMarketSheet.Columns(rCount, "10") = oMarketSheet.Columns(rCount, ColAccntB) ' = j
                             Accnt = "010-41200"

[RobDog888]

Depending on how you have your column formatted (Date, Number, or Text) this will probably help.

Dim rCount As Range
  Dim ColDate As Date
  Dim ColAccntB As Integer
  Dim ColAccntE As Integer
  Dim ColRev As Integer
  Dim Accnt As String
  Dim rRow As Range
  Dim rCell As Cell

We need to tackle the errors one at a time. Get those working then we can
move on to any others.

[opie_18]

This user type has to be defined for the Cell and Range... how do I do that?

Dim rCell As Cell
Dim rCount As Range

[RobDog888]

Are you writting the code in the VBA Editor (behind Excel) or from Visual Basic?

[opie_18]

got it...

Thanks

[RobDog888]

Ok, what was the final issue?

Glad to have helped!

ps, dont forget to Resolve your thread